Ga naar inhoud

Conventions

Rules

  • Each table has a unique prefix for it's fields.
  • Each table has an ID field as a unique identifier (primary key)
  • A foreign key refers to the ID field of the target table, and has FK as suffix.

Tables

  • Tablenames may not contain underscores, use camelcase, javascript convention.
  • Tablename is based on a single item, no plural

Fields

Rules for fieldnames: - Use camelcase, javascript convention. - Foreign keys end on 'FK' - boolean fields start with 'Is', 'Has' or 'Allow' - datetime fields end on 'On', or 'Start' and 'Stop' for a period - Use as much as possible not null, with adding default (empty string or 0). Date fields can not have a default.

All tables will have the following fields (xx is the prefix of the table, always lower case)

  • xxID : An integer that is the unique key for the record with default next value for seqTablename
  • xxCreatedOn : The date and time that the record is created
  • xxCreatedBy : The user that created the record
  • xxChangedOn : The date and time that the record is last changed
  • xxChangedBy : The last user that changed the record

Indexes

  • Primary key name is pkTablename
  • Unique index name is udxTablenameFieldname without field prefix
  • Normal index name is idxTableNameFieldname without field prefix
  • Indices with more than one field, use descriptive name after idxTablename or udxTablename

User defined types

  • names in camelcase, with starting capital

Sequences

All tables have a sequence named seqTablename defined as

create sequence seqTablename as bigint start with 1;