Column Names and Other Best Practices
1. Each column name must be derived from the business name identified during the business/data analysis process. If the column was not identified during the analysis of business data, it must still be given a spelled-out logical name; e.g., FirstName, MiddleInitial, PhoneAreaCode, etc.
2. Column names should be descriptive and clearly understandable by someone who looks at them for the first time.
3. Each word in a column name is capitalized, and, for the sake of clarity, can be separated by an underscore: e.g.,
4. Column names should contain letters, numbers, and underscore characters (“_”) only: e.g., UserHistory_2008.
5. If multiple fields in a table reference the same primary key in another table, the field names should be followed by a suffix. (Example: fk_ProviderID_From fk_ProviderID_To)
6. Column names should be unique throughout the database, if possible.
7. The primary key Column should always end with “ID” and begin with “pk_”. If the Column is not auto-numbered, all relationships to that Column must have referential integrity with a cascading update.
8. A Column referencing a primary key Column in another table should have exactly the same name as the Column it is referencing from the reference table plus “fk_” as a prefix to denote foreign key.
9. Column names should not be abbreviated. (Exception if the Column name is too long to be understood)