Essential database naming conventions

There are a few simple rules that i use for naming database objects (including the name of the database itself).

General

• User lowercase characters. Eliminates question of proper case as well as errors related to case-sensitivity and speeds typing rate and accuracy.
• Separate words and prefixes with underlines, never use spaces. Promotes readability and offers greater platform independence. Avoid having to bracket names (e.g. [book name] or "book name").
• Do not use dashes in database names.
• Avoid using numbers.

Database name

[system name]_[customer or purpose]

Table name

[schema].[table name]

• Give tables singular names, never plural.
• Use schema - it allows to group database tables.

Field name

[field name]

• The primary key should be the singular form of the table name suffixed with '_id'.

Primary key name

pk_[table name]

• Always use the 'pk_' prefix for primary key name.

Index name

ix_[table name]_[index field names]

• Always use the 'ix_' prefix for index name.

Foreign key name

fk_[table name]_[foreign key field names]

• Always use the 'fk_' prefix for index name.

Database example


I added a simple database diagram which has three tables: 'person', 'organisation' and 'account' and it has two namespaces: 'demographic' and 'identification'. Person has relationship with organisation.