Naming Conventions in Database Design

Even though I am not a professional database administrator, I have designed a number of databases. One thing that always plagued me when doing this is the lack of a common convention that can be used to create the database design. So, I have decided to create one for myself.

You may not agree with all the rules given here - feel free to copy this list and create a set of rules for yourself. But once it is created, make sure that you stick to it.

Database Name

• Name of the project. Eg. nexty

Table name

• Singular. Eg. User
• First Letter uppercase Eg. User
• Word separation in Camel case(first upper) and underscore(_). Eg. User_Image - (Different rule for reference tables)

Field Name

• Singular
• Completely lowercase
• Word separation using '_'. Eg. 'first_name'
• No prefix.

Naming Conventions

• Postfix date fields with '_on'. Eg. added_on
• Primary key should be always 'id'
• The title field in the table must always have the name 'name'. Eg User.name, Page.name, Content.name
• Reference table must be named in the format <First table><Second table> - Eg. PageTag
• Foreign key must be in the format '<table>_<primary key>' - all lower case - Eg. user_id

Default field names

• id
• name
• first_name
• last_name
• email
• username
• password
• description
• url
• address
• city
• state
• country
• sex
• added_on
• edited_on
• image
• file
• amount
• status

Further Reading

• Naming Conventions
• Plural vs Singular Table Names
• Database Naming Conventions