===== 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" [[..:..:audit:dev:audit#user_transaction|transaction identifier]] - entities was saved in this context (batch). It's not related to database transaction.
    * ''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.