10 Database Naming Conventions Best Practices

If you are involved with SQL and want to simplify it, are tired of conflicting information about data integrity, or just want to make database programming easier, then it is important to know these database naming conventions best practices. If you take a look at the average piece of content on the internet today on this subject, you’ll find that most of the information either conflicts or doesn’t make a lot of sense. Let’s clear that up today.

1. Consistency is always the best policy.

One of the most popular pieces of advice is to equip columns with unique names in a database. The thought process seems good on paper: the unique name gives you a natural reference point so that you can find any specific column at any specific time. The only problem is that unique names eventually become difficult to find. A good practice is to just name columns that are based on their function. If your column is for a description, then just called it “Description.”

2. Every table should have its own row identifier.

If tables are joined against multiple columns for a single entity, then trying to enforce any database constraints becomes a migraine in the waiting if the parent row has a compound key. Many databases don’t even support foreign key constraints, making them virtually impossible for data access from an end user standpoint. If you give each table its own row identifier, you’ll be able to clean up the programming while making the naming conventions a whole lot easier to understand.

3. Plural or singular names don’t really matter.

This all comes down to personal preferences and how people think. Some people need to have plurality for the name to make sense. Others see having plural names as the waste of a character. What matters more is that whatever process you use for naming, it needs to remain the same and be readable in the format that you choose. Some people like to put multiple word names into one word with capital letters separating the word. Others prefer to use dots, while still others like to use an underscore to make the entire process easy to read and understand.

4. Prefix names make sense when you include them as an indication of the object type involved.

If you have multiple modules that all have similar tables that must be organized, then allowing for a table prefix to distinguish between the different subjects will help everything make a lot more sense. It’s important, however, to make sure that if you use abbreviations for these different modules that the abbreviations make sense. If you’re abbreviating a security module, using “ITY” might make sense to you since its the last three letters of the word, but the average person is going to look at that like you’re writing in a foreign language.

5. Never allow the database to put in the constraint names automatically.

If you allow the automatic constraint names, then you’ll find that they add a random number at the end of it. This especially a problem if you have a production database that is outside of your office. You’ll wind up with two databases that have two different constraint names and merging them together into a cohesive unit is virtually impossible. You don’t really want numbers in your name in the first place, so don’t get into the lazy habit of not typing in a name. You’ll pay for it later on – guaranteed.

6. LNV naming conventions is often the standard that is used, even if Microsoft Office or Visual Basic is not being used.

The LNV naming conventions are widely used almost everywhere. If you are not familiar with them, then having an appendix on hand for the naming convention tags for objects will be a useful informational reference. In particular, the tags for access objects is incredibly useful because many naming conventions across the board are similar. If you include these in your database, then you will make it user friendly for those who may need to access it later on in the future.

7. Use plain old English whenever possible.

Having a number of abbreviations might make sense to you, but HAZENPLB isn’t going to make a lot of sense to other administrators who come across your work. People shouldn’t feel like they need to have a decryption ring with them to decipher what you meant. The reason for these names is because there used to be an 8 letter requirement in naming. That is long gone, so if you need to have a field that involves customer zip codes, you can literally name it CustomerZipCode.

8. Spaces are just bad news.

Anything is better than a space in an object name. Some platforms allow spaces to be included in a name, but they can cause a massive programming headache later on down the road – especially when it comes time to migrate data.

9. Follow the rule of pillar data.

Field names that are date specific will quickly become outdated and useless. Instead of assigning a year, just assign a name that makes sense for the field. If you’re keeping track of zip codes, to use the example above, don’t say CustomerZipCode2014. Just keep it plain and simple and you’ll have a better overall experience.

10. Avoid being redundant so you can avoid being redundant.

It can be easy to get into the habit of naming items based on certain prefixes that are needed and names that are being used. The only problem is that this can often create a redundancy within your name that can make it difficult to access later on. If you need a zip code report object, then calling it rptZipCodeReport will just make people shake their heads when they see it.

Most importantly, if you have a question regarding names that you see of names that you should assign, never assume what the local best practices happen to be. Get a second opinion so that the data can be effectively used. If it makes sense to your database administrator and your friend who has no clue about what you do, then you’ve found a good name.

The goal of the database naming conventions best practices is to create names that are consistent, readable, and easy enough to understand. These 10 tips will help you do just that.