ColdFusion MX Coding Guidelines - Database Conventions

Release 3.0.2 (10/17/2003)

« Good Practice | Contents | Globalization »

Database Conventions

This section provides guidelines on database table structure and naming.

General Database Naming Issues

1. Table names should be singular (e.g., CUSTOMER_ADDRESS, ORDER_LINE_ITEM).
2. Table names may be repeated (as prefixes) in column names only if doing so makes the column names more readable, e.g., CUSTOMER_ADDRESS_ID, ORDER_LINE_ITEM_ID, but in general should be avoided.
3. Column names should be singular and descriptive (e.g., CUSTOMER_FIRST_NAME, PRODUCT_DESCRIPTION).
4. Abbreviations should be avoided wherever possible (with the exception of the primary key abbreviation discussed in 7).
5. Primary key column names should either be the table name followed by _ID or simply ID (or OID for "object ID") and should be the first column of a given table (e.g., CUSTOMER_ID, ORDER_ID). The corresponding constraint name should be the table name followed by _PK (e.g., CUSTOMER_PK, ORDER_PK).
6. Foreign key column names should match their referenced column names (e.g., foreign customer key in ADDRESS table should be called CUSTOMER_ID, i.e., ADDRESS.CUSTOMER_ID identifies CUSTOMER.CUSTOMER_ID, CUSTOMER.ID or CUSTOMER.OID). The corresponding constraint names should be the table name followed by _FKn where n = 0..9 (e.g., ADDRESS_FK1, ADDRESS_FK2)
7. Surrogate keys should be used only when there is some added benefit. In other words, many to many join tables do not need primary keys unless some value is added.
8. Related columns should be grouped together (e.g., [FIRST_NAME, MIDDLE_NAME, LAST_NAME] - [PHONE_NUMBER, FAX_NUMBER, PAGER_NUMBER]).
9. In general, all tables should contain DATE_CREATED and DATE_LAST_UPDATED columns.

« Good Practice | Contents | Globalization »

Source: http://livedocs.macromedia.com/wtg/public/coding_standards/database.html