Wednesday, March 9, 2011

Essential database naming conventions

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

  • 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.

No comments:

Post a Comment