7.7:dev:conventions:database-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 - transaction identifier
      • 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)
    • Data lengths and types:
      • varchar(255) - name, code, etc
      • varchar(2000) - description, long text
      • number(38,4) - currency, "real" numbers

For the automatic creation and modification of the database scheme, the database scripts is used.