Not again you sigh... 🙂
Well, its on for young and old and im in the thick of it yet again. Table and column naming conventions are what I am after as far are recommendations and tried and tested schemas. I have gone for the following for sometime now:
Tables : name as required to represent the business, eg. trade
P.Keys : post-fix with _id, eg. trade_id
F.Keys : the joining column is listed as part of the column name
eg. traderto_trade_id this example means the table “trade_rto” joins to “trade” over “trade_id”
The first portion (column prefix) should shorten to <= 8 characters in length with no spaces or underscore characters. For example:
Employer => emp_<column name>
Employer_site => empsite_<column name>
Organisation -> org_<column name>
The 8 character restriction is for the tables prefix, not the entire length. Even so, the entire length should not exceed 30 characters if possible.
Another schema design we have in another app database uses the following:
The other uses a slightly different approach. The only difference in this method is the naming of columns, which is especially evident in the key-column referencing. For example:
Training_Product ----------------------- Course
Training_product_id (pk) Training_product_id (fk)
In this case, as the training product table is the parent, all other referring tables use the same primary key column name for the foreign key column name. This is a common scheme, but unfortunately does not allow a developer to “follow the relationship model” from one table to the next and thus, for large data models, can be time consuming when creating large complex queries. Another problem is that extensive use of table aliasing is required in such a scheme.
I have also heard of schemes where the data type of the column is in the naming of the column. This seems very strange and something I wouldnt like to explore for a variety of reasons.
What I havent gone into is issues of replication, and inter-related schemas with the same table names.
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"