Skip to Content

Conventions of database design

Last modified by tomiskar on 2019/06/11 12:59
  • 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 - 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 database scripts is used.