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 by 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 an 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. This will make specifying joins simpler and less error-prone.
Consider using domains to standardize column size, type, and scale. Each domain should have a standard suffix.
Surrogate (and surrogate like) keys should have an 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 where 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 generated using the rules applicable to tables. Views are typically prefixed with V_ or VW_.
Key Constraint and Index Names
Use standard name 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 the 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.