Differences

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

--- kb:bestpractices:codingconventions:database [2026/10/06 13:23] – 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 =====
-Default columns for tables include:
+==== Naming ====
+  * Use lowercase ''snake_case'' for table and field names.
+  * Use singular nouns for table names.
+  * Use descriptive names and avoid prefixes such as ''tbl_''.
+  * Do not repeat the table name in field names.
+  * Avoid SQL and DBMS reserved words.
+==== 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 ====
-^ Field Name              ^ Description         ^
+  * Always use parameterized queries for external values.
-| ''id''                  | numeric, unique identifier   |
+  * Never construct SQL by concatenating external input.
-| ''created_on''          | datetime   |
+  * Explicitly select required fields instead of using ''SELECT *'' where the result structure matters.
-| ''created_by''          | username/id   |
+  * Add indexes according to actual query and relationship requirements.
-| ''changed_on''          | datetime   |
-| ''changed_by''          | username/id   |
-| ''status''              | ''0'' = disabled; ''1'' = enabled   |
+==== 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.