Creating the db

NAMING CONVENTION

TABLES

Every table will have a name in plural representing the entity stored. It will be all lowercased. (eg. contacts)

REGULAR TABLES

For each table created will to have a 3-letter id to use as a prefix on every table's field. e.g. account table will have acc prefix, and the id field will be acc_id

N:M TABLES

These tables will be named as [table1]_[table2], and will have a 6 letter prefix which will consist of the concatenation of the prefix of both tables

FIELDS

FIXED CASES

Where xxxxxx is a table, and xxx its prefix:

Id

Every table has to have and id field xxx_id, integer not null and autoincremental

Name

If it applies, every table should have a name field, not null and varchar(50)

Foreign key to table yyyyyyyy with yyy prefix

If there's only one reference to the table, fk field name should be xxx_yyy (integer, not null)

Audit

• xxx_createdby (integer, not null. reference to user)
• xxx_createdon (datetime)
• xxx_lastupdatedby (integer, not null. reference to user)
• xxx_lastupdatedon (datetime)

Status

All records (except for N:M tables) will have a status fields, indicating if the records is live or soft deleted.

• >0 deleted record (different values might show diferent deletion reasons. to be documented)
• =0 live record
• <0 reserved

N:M TABLES

Where xxxxxx is table 1, and yyyyyyyy is table 2, and their prefixes are xxx and yyy:

• Table id: xxxyyy_id
• Table 1 fk: xxxyyy_xxx
• Table 2 fk: xxxyyy_yyy

TABLES LAYERS

Tables are divided in groups. By now:

• Security Tables (users, permissions, sessions, etc.)
• Master Data Tables (everything else)

The security tables will have the "security_" prefix in its table names and the "s" will be the first leter in their 3-letter prefix

RECORD OWNERSHIP

If this feature is required in table, there has to be a field xxx_owner, integer, with the follwing values:

• 0: public record
• null: only visible for admin
• #: the id of the user owner of the record