Differences

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

--- devel:documentation:conventions:dev:database-conventions [2018/05/22 11:49]
tomiskar [Conventions of database design]
+++ devel:documentation:conventions:dev:database-conventions [2019/06/10 10:27]
tomiskar
@@ Line 1: / Line 1: @@
+===== Conventions of database design =====
+{{tag>conventions}}
+  * DB objects (charts, indexes, etc.) of the idm **core** always have the prefix ''idm''
+  * DB objects (charts, indexes, etc.) of the **module** always have the prefix according to the module (e.g. ''acc'')
+  * DB objects (charts, indexes, etc.) **are called in the singular using underscore signs, all in small type** (e.g. ''idm\_identity\_role'')
+  * DB **names** of objects (charts, indexes, etc.) must be of a **maximum of 30 characters** (compliance with the limitations of some databases). As for the charts with an audit, you need to bear in mind that a chart with the suffix ''a'' will be created in the database => the names of the audited charts must have a **maximum of 28 characters**
+  * As a **primary key** we use the ''uuid'' generated on the application logic (for summary, see the next chapter).
+  * **We do not create foreign keys** - the reference integrity is hold within the application (supposing the use if the non-relational database for a part of the application). The database is used as a data warehouse.
+  * **indexes are created** to search for identities according to the links (if the search is based in the links).
+  * Naming the indexes:
+    * prefix ''idx\_'' - for a "standard" index
+    * prefix ''ux\_'' - for a "unique" index
+  * Naming the attributes:
+    * ''id'' - identifier
+    * ''created'', ''creator'', ''modifier'', ''modified'', ''creator\_id'', ''modifier\_id'', ''original\_creator'', ''original\_creator\_id'', ''original\_modifier'', ''original\_modifier\_id'' - audit informations
+    * ''transaction\_id'' - "User" transaction identifier - entity was saved in this context (batch).
+    * ''realm\_id'' - owner organization
+    * ''name'' - name, display name
+    * ''code'' - code, application usage, could be unique (''Codeable'' interface)
+    * ''main'' - naim / dafault flag
+    * ''disabled'' - we preferred **disabled** flag before enabled / active etc. Entity is active by default (''Disableable'' interface)
+    * ''valid\_from'' - entity validity start (''ValidableEntity'' interface)
+    * ''valid\_till'' - entity validity end (''ValidableEntity'' interface)
+    * ''external\_id'' - unique external id (''ExternalIdentifiable'' interface)
+    * ''external\_code'' - unique external code (''ExternalCodeable'' interface)
+  * Data lengths and types:
+    * ''varchar(255)'' - name, code, etc
+    * ''varchar(2000)'' - description, long text
+    * ''number(38,4)'' - currency, "real" numbers
+===== Database scripts =====
+For the automatic creation and modification of the database scheme, the [[..:..:architecture:dev:flyway|database scripts]] is used.