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
- username
- password
- description
- url
- address
- city
- state
- country
- sex
- added_on
- edited_on
- image
- file
- amount
- status
