Database Naming Conventions

Naming standards  for database objects can simplify development. The names used for tables and columns are most visible. Using standard conventions for the other objects can clarify where they fit in the design. This article describes the conventions I use be default.

Table Names

Table names should be descriptive of their content. Tables contain collections of records, the name should describe the collection. Try to choose names applicable to the whole set, or at least the majority of the set. Avoid using keywords for table names. (NOTE: order is a keyword, orders is not.) Core tables should have short names.

Define additional information for each table:

  • Singular form: (used for child tables, related code tables, and surrogate key column name).
  • Abbreviation: (used for foreign key index names, and join tables). Use standard abbreviations where possible, and use a standard method for the rest.
  • Very short form: (used as alias in DML joins). Use initials for core tables, add additional characters for other tables.
  • Description of table contents and usage. Include details of records not covered by the table name (i.e. credit notes in the orders table).
  • Consider documenting the record life-cycle, update conditions, retention requirements, and archiving schedule.

Column Names

Column names should describe the contents of the column. Standard suffixes help describe the content type.

Use a standard set of columns for code tables. Suffixes may be omitted for these columns unless needed to differentiate columns. Always prefix the primary key column with the name of the code table.

Consider using domains to standardize column size, type, and scale. Each domain should have a standard suffix.

Surrogate (and surrogate like) keys should have appropriate domain based on usage. Code tables, core tables, and child tables have significantly different volumes. Define domains for each. These domains could share a common suffix such as _id. Typically the column name should be the singular form of the table name with the domain index.

Migrated keys should retain their original name were possible. Prefixes where required should indicate the reason the column was migrated. When migrated columns collide with columns in the target table, prefix with the singular form of the table from which the column ultimately originates. Name collisions should be limited.

Views Names

Many view names can be generate using the rules applicable to tables. Views are typically prefixed with V_ or VW_.

Key Constraint and Index Names

Use standard naming conventions for key constraints and index names. Key constraint indexes should have names differing only in prefix from the key constraint. Consider the following standards :

  • Primary keys: Prefix table name with PK_.
  • Primary key indexes: Prefix table name with PKI_.
  • Unique (alternate) keys and indexes : As per primary key replacing P in prefix with U. Add a count if multiple alternate keys exist.
  • Foreign keys: Join source and target table abbreviations and prefix with FK_.
  • Foreign key indexes: As per foreign keys using the prefix FKI_.
  • Other indexes: Prefix table abbreviation with IN_. Use suffixes if required. Consider using a purpose abbreviation or count as a suffix.