Database Naming Convention

Why use a database naming convention?

Ever worked on databases that had some tables and columns with underscores for word separators and others without? How about primary keys named ‘id’ on one table and another table its the name of the table followed by ‘_id’? And function names that don’t relate to the action they are performing?

All of these situations make working with a database much harder than it needs to be. If consistency is introduced then it’s possible to make a reasonable guess at what the table name would be, what the column name would be and what the function actually does.

Is there an ideal convention to use?

Well it would be great if there was but every database is different, they come in all different shapes and sizes and there is a huge variety on what information is stored. What is important is to have a convention. So bring your database team together agree on a database convention, write it up and stick to it. Review it regularly to ensure conventions have been adhered to and make any adjustments.

My Suggested database naming convention

After working with databases over a number of years I have researched and implemented many database conventions and the one I use now on a small database of 150 tables and 2600 columns is detailed below.

General Naming Conventions

1. Character count: The database will usually have a limit but it should be kept under 30 characters, the shorter the better
2. Use only letters and underscores, avoid numbers
3. First character is a letter, ie not an underscore
4. Use only lowercase characters
5. Separate words with underscores
6. Don’t use spaces
7. Avoid abbreviations that are not widely used within the organisation
8. Avoid acronyms as they may have more than one meaning
9. Name should be readable and it’s meaning understood
10. No spelling mistakes
11. Primary key should always be called ‘id’
12. Be consistent

Table Naming Conventions

1. Singular
2. Group related tables together with two or three letter prefixes. AS NEEDED
3. Use consistent suffixes and prefixes
   • Suffix history tables with ‘hist’ and audit tables with ‘audit’

Field Naming Conventions

1. Singular
2. Use consistent column name suffixes and prefixes
   • Suffix date fields with ‘_date’ Eg created_date
   • Suffix datetime fields with ‘_datetime’ Eg. created_datetime
   • Suffix timestamp with timezone fields with ‘_tstz’ Eg. created_tstz
   • For boolean fields that require a bit more clarification use “is_” or “has_” prefix
3. Use <tablename>_<primary key> for foreign key columns
4. Constraints are to use <tablename>_<columnname>_<suffix> Eg person_organisation_id_fk. Suffixes are
   • _pk    primary key
   • _fk    foreign key
   • _ck    check
   • _uq    unique
   • _idx    index

View Naming Conventions

1. Prefix with ‘view_’

Function Naming Conventions

1. Use a verb and noun combination to describe function. Eg create, generate, delete, get

Trigger Naming Conventions

1. Name with <table_name>_<when><event>[_<column name>]
   • when: b = before, a = after
   • event: i = insert, u = update, d = delete
   • Eg customer_biud, organisation_au_name

Summary

Setting up a database naming convention may take an hour or two and implementation a few hours or days depending on the changes and size of the database. But the results will pay for themselves as it will make you more productive when you use and maintain the database.

Do you use a database naming convention? How is it different to what to I have outlined? I would love to hear what you use and if it has helped your productivity so please leave a comment or send an email directly to me at carmen@carmengrantham.com.