Naming Conventions in Database

 

What’s In A Name? The Database Edition

Database models require that objects be named. While several facets of naming an object deserve consideration, in this article we’ll focus on the most important one: defining a convention and sticking to it.

Why Use Naming Conventions?

Look at the database model below. I went a bit overboard and removed as many traces of a naming convention as I could. This proves my first point: a naming convention is an important part of a well-built data model. The model is not very fun to look at, to try to understand, or to code around.

EDIT MODEL IN YOUR BROWSER

Planning A Naming Convention: Table Elements

Let’s start with some things that you should consider if you need to create a new naming convention. You’ll want to name all the obvious database elements:

• Tables
• Views
• Columns
• Keys – including the primary key, alternate keys, and foreign keys
• Schemas

But don’t leave out the less-visible items:

• Tablespaces
• Constraints
• References
• Indexes
• Stored procedures
• Triggers
• Sequences
• Variables

You now know what to name. How will you decide on your protocol?

Consider all the decisions that are involved. For table, view, and column names you have to decide on:

• The case of the name. You can choose between:
  • UPPERCASE names
  • lowercase names
  • camelCase names – the name starts with a lowercase letter, but new words start with an uppercase letter
  • PascalCaseNames (also known as upper camel) – similar to camelCase, but the name starts with an uppercase letter, as do all additional words
• How to separate words in names:
  • you can separate them by case (starting each new word with an uppercase letter)
  • you can separate them with an underscore (like_this)
  • you can separate them with spaces, though that is very uncommon
• Whether to use singular or plural names
• How to use prefixes:
  • with module names
  • with Hungarian notation (prefixing objects with metadata about their type)

Planning A Naming Convention: Columns, Keys, and More

When naming columns, pay special attention to key columns:

• For primary key (PK) columns: Are artificial PK columns called id, ID, table_name_id? Do you use artificial PK columns at all?
• For foreign key columns: Are they called book_id, bookID, etc?

Make sure that whatever you decide can be consistently applied throughout the model. These conventions also should be compatible with your general naming convention. For example, if you choose uppercase names for tables, then key columns and all other items should also be uppercase.

Now, take a look at constraints. A typical naming convention for constraints mentions the type of constraint, the name of the table, and the names of columns involved. It is important to name constraints so that when the constraint is violated, you know which constraint has been violated and which table is involved.

Your constraint conventions might look like this:

• PK_TableName for primary key constraints
• FK_TableName_ReferencedTableName[_n] for foreign key constraints
• UQ_TableName_ColumnName[_ColumnName2...] for unique constraints
• CK_TableName_ColumnName (or CK_TableName_n) for check constraints

Similar rules apply to the naming of indexes, stored procedures, triggers, and sequences. A stored procedure name should be meaningful, explaining briefly what the procedure does (like uspGetAccountBalance). An index name should include the index type, the name of the indexed table, and indexed column names. It would look something like this:

IX_TableName_ColumnName

Trigger names also need to be explanatory. A trigger can be a before trigger or an after trigger. It can be associated with an insert, update or delete action. To keep things simple, a trigger name should be made up of the table name and an acronym representing the triggering action. In this convention, a before-insert trigger would be:

Table_Name_BIS_TRG

Usually a sequence is used to populate an artificial primary key column for a certain table. A common convention is to use TableName_SEQ for the name of the sequence.

At the Beginning, Consider the End

As you choose a naming convention, consider how the final database will be used. A lot of things will have an impact on your naming scheme. Will the database be accessed by a modern programming language or by legacy systems? If legacy systems will be used, make sure your conventions match up with their standards.

If you plan to use plain SQL, establish conventions that make querying in SQL easy. In particular, avoid names which force you to use quotation marks (example: keyword names, or names with spaces) and mind your uppercase and lowercase letters. SQL is a bit strange with respect to case-sensitivity, so it’s often best to use underscores to separate words.

If developers use object-relationship mapping (ORM) tools to access the database, additional factors come into play. It is generally easier if table and column names do not have spaces in them. Imagine how Hibernate would deal with that convention.

Setting Up Easy-to-Use Conventions

Naming conventions are partly a matter of personal choice. However, often a naming convention may already exist within your project or within your company. Most companies like consistent models that can easily be combined with consistent naming.

At my place of work, we recommend a particular convention:

• We include a shortened version (3 letters) of the business domain or module name to start each table name so that it’s easier to understand to which domain this data relates.
• We use singular forms rather than plural (“message” not “messages”).
• We keep our table names relatively short.
• The names of foreign key columns contain an abbreviated version of the table name.
• We avoid spaces in object names.
• Also, we avoid using SQL and database engine-reserved keywords as identifiers (i.e., names of databases, tables, indexes, columns, aliases, views, stored procedures, partitions, tablespaces, and other objects.)

You need to decide the conventions that you want to use. And be logical in your naming. A logical convention will make things easier across the board.

Let’s look at our first model for users, roles and permissions – this time, with some conventions applied.

EDIT MODEL IN YOUR BROWSER

You’ll likely agree that that is much easier to understand and work with.

In this model, I decided to use camelCase for table and column names and singular table names. My reasoning was that the developers are working in Java, and their method naming convention also uses camelCase. In these instances, I prefer to have an “id” column as my primary key so that nature keys can be easily changed – i.e., I can easily rename a group or a role.

Below you’ve got another example showing a different naming convention in which I separated words with underscores (e.g., user_account), and used lowercase names only:

EDIT MODEL IN YOUR BROWSER

Using Naming Conventions

Solidifying and following naming conventions may not seem significant during the creation of a data model. Yet consistent names are the entry point of any model. They provide an overall insight into the task and should be kept constant.

There is no reason to be inconsistent in your naming except sheer laziness. Naming elements any old thing may seem easier, but this will only frustrate those who must update the model in the future. And remember: the person who needs to update the model after some time has passed might be you. You will thank yourself if you followed a consistent naming approach. It will be easier to remember what you were thinking during your modeling, which will in turn make the updating process less demanding.

Make your conventions strong and logical. Define when to use artificial keys and how to name them. For example, some companies prefer to call artificial keys “ID”, but others consider this something between an anti-pattern and bad practice. Whatever you choose, stick with it. If your conventions are weak, this can lead to errors.

Plan for Exceptions

There may be situations that call for an exception, little as we want to make them. Maybe your convention is to name that artificial keys “ID”. You have columns to store in the table that are also called “ID” in the business domain. Maybe you store an End-to-End Reference ID, a Shipping ID or an Invoice ID. Should you rename these columns so that you do not confuse them with the artificial key for the table? For example, should you change Invoice ID to InvoiceNumber and Shipping ID to ShippingReference? No, definitely not. Just make an exception. In this case, rules are made to be broken, or at least bent.

Your convention might state that “ID” should be the name for the primary key of each table. Or it might dictate that “ID” is part of the name of a key, like “TransactionID”. Yet, there may be good reasons to have a table with “ReferenceNumber” as the primary key. Maybe the business refers to the domain object in that way. In financial services, financial messages include a transaction reference number. The Transaction Reference Number is familiar to people working in this business domain. Don’t cause yourself problems by forcing the primary key to be “MessageID”.

Of course, you must only allow yourself to create an exception when you can justify it. Then be sure to document the exception and why the convention was not followed.

Naming Conventions + Consistency = Best Practice

Naming conventions are a matter of personal choice. What you finally decide on is only valuable when decisions are consistent and documented. A well-designed model adheres to a naming convention, but the occasional exception can be permitted if it can also be justified.

Now, let’s open this up for some discussion. What are your views about naming conventions? What naming convention do you follow? Share your views in the comments box below.