[eluser]kimon[/eluser]
Hi All,
after some (many) years in designing database for large systems (> 1,500 tables) in various database engines, I have concluded and used the following:
- Each table represents an entity so it is in singular (e.g Customer). It's database table name will be CUS. That is the TLA (Three Letter Abbreviation) of the entity name.
- Each field of Customer Entity, thus CUS table, will start with TLA of the entity. CUSID, CUSCODE, CUSNAME, etc.
- Foreign Key fields in that entity / table, are the exact field names of the primary key that references, e.g. PRFID which is the foreign key field to the PROFESSION entity / table that has the TLA, PRF.
- table constraints (Primary Keys, Foreign Keys, Unique Keys, etc) will be prefixed with the entity / table name, underscore, then the constraint type and as follows (in the example context of Customer) and then (where applies) the field(s) used: CUS_PK, CUS_FK_PRFID, CUS_UNQ_CUSCODE
- possible views, stored procedures or other objects (depending on the database engine: like generators in firebird/interbase, sequences and packages in oracle, etc) will be prefixed or will include the entity TLA that belongs
Of course in large scaled projects with many entities / table and many people on the project team, some tools are necessary to help with this rules. Apart from the design documents, a TLA database is a must have as part of a good documentation on this rules. :-)
That's my part of the story of the database naming conventions I use.