View comments | RSS feed

ColdFusion MX Coding Guidelines - Database Conventions

Release 3.2 (4/14/2005)

« Good Practice | Contents | Globalization »

Printable Version

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 »

Comments


No screen name said on Dec 28, 2004 at 4:48 PM :
With regard to ColdFusion MX Coding Guidelines - Database Conventions, naming conventions for table primary keys: Wwe have been particularly loyal to Id_TableName. This naming convention makes it very clear in related tables that the field is a Foreign Key and to what table it belongs. Naming the primary key ID for every table can be very confusing , especially in many-to-many situations.

 

RSS feed | Send me an e-mail when comments are added to this page | Comment Report

Current page: http://livedocs.adobe.com/wtg/public/coding_standards/database.html