Conventions of database design
- 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
- identifiercreated
,creator
,modifier
,modified
,creator_id
,modifier_id
,original_creator
,original_creator_id
,original_modifier
,original_modifier_id
- audit informationstransaction_id
- transaction identifierrealm_id
- owner organizationname
- name, display namecode
- code, application usage, could be unique (Codeable
interface)main
- naim / dafault flag
Database scripts
For the automatic creation and modification of the database scheme, the database scripts is used.
We should probably add something about the Flyway script naming convention.