Differences

This shows you the differences between two versions of the page.

--- kb:bestpractices:codingconventions:database [2026/10/06 13:20] – joerg.hampel
+++ kb:bestpractices:codingconventions:database [2026/10/06 13:28] (current) – joerg.hampel
@@ Line 3: / Line 3: @@
 ===== Architecture =====
-  * Wrap each query in a separate SubVI
+  * Wrap each query in a separate SubVI for reuse and documentation.
-    * this allows for easy reuse
+  * Where useful, convert query results into typedef'ed clusters.
-    * add documentation to describe the returned values
+  * Database connections are shared resources and should not be used in parallel.
-    * optional: convert the returned 2d-array of strings into a 1d-array of typedef'ed cluster
+  * Use one DB connection instance per consumer to avoid blocking.
-  * A DB connection is like a shared resource that cannot be used in parallel. (also true for SQLite)
-    * use one db connection instance per consumer to avoid blocking
 ===== Design =====
-  * Usual default columns for tables include
+==== Naming ====
-    * id
-    * created_on
+  * Use lowercase ''snake_case'' for table and field names.
-    * created_by
+  * Use singular nouns for table names.
-    * changed_on
+  * Use descriptive names and avoid prefixes such as ''tbl_''.
-    * changed_by
+  * Do not repeat the table name in field names.
-    * status
+  * Avoid SQL and DBMS reserved words.
-      * 0 .. disabled
-      * 1 .. active
+==== Keys and Relations ====
+  * Use ''id'' as the primary key.
+  * Name foreign keys ''<referenced_table>_id''.
+  * Define foreign-key constraints wherever possible.
+  * Name many-to-many relationship tables after the related entities.
+==== Standard Fields ====
+Default fields where applicable:
+^ Field ^ Description ^
+| ''id'' | Unique identifier / primary key |
+| ''created_on'' | Creation date and time |
+| ''created_by'' | Creator |
+| ''changed_on'' | Last modification date and time |
+| ''changed_by'' | Last modifier |
+| ''status'' | Record state |
+Only add standard fields where they serve a purpose.
+==== Data ====
+  * Use ''NOT NULL'' unless the absence of a value has a defined meaning.
+  * Use appropriate native datatypes instead of formatted strings.
+  * Include units in field names where otherwise ambiguous.
+  * Use descriptive positive names for boolean fields, e.g. ''is_active'' or ''has_error''.
+  * Store timestamps in a consistent timezone; prefer UTC for distributed systems.
+  * Use database constraints such as ''PRIMARY KEY'', ''FOREIGN KEY'', ''NOT NULL'', ''UNIQUE'' and ''CHECK'' to enforce data integrity.
+==== Queries ====
+  * Always use parameterized queries for external values.
+  * Never construct SQL by concatenating external input.
+  * Explicitly select required fields instead of using ''SELECT *'' where the result structure matters.
+  * Add indexes according to actual query and relationship requirements.
+==== Schema Changes ====
+  * Keep schema changes reproducible and version controlled.
+  * Define and track the database schema version where databases can evolve after deployment.
+  * Test migrations against representative existing databases before deployment.