Creating the db -- Naming Convention

1. TABLES

Every table will have a name in plural representing the entity stored. It will be all lowercased. (eg. contacts)
1.1 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
1.2 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

2. FIELDS

2.1 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

2.2 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

3. 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

4. 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
• number (not zero): the id of the user owner of the record

[augusto wloch]

mmmh... see no diference between a record with null owner (only visible for admin) and a record with owner = n (where n is the user id for admin). because of this finding, i'm cancelling the null value fro record ownership