Tracker Ten Database Naming Conventions Best Practices

Database Naming Conventions Best Practices

Naming conventions are important in all aspects of software development, including database development. When you are creating your database, you will need to assign names to tables, columns, attributes, fields, views, keys, stored procedures, indexes and more. Selecting good names can make your database a lot easier to work with. Therefore, it’s always a good idea to start with a set of rules that help you select solid names for database elements and schema. This set of rules is a naming convention.

If you carefully follow your rules, your queries will be easier to read and your reports will make more sense. Although it’s possible to rename after your database goes live, it’s much more efficient to choose the right names for your database elements from the beginning. If you try and change names or syntax after your database goes live, you’ll need to change every reference to the name.

Pseudocode Naming Conventions

Before you formally design a database or schema, you may sketch it out on paper or in a software design tool, ORM or workbench. Even at this stage you should adopt proper names, as it will reduce the time and effort needed to convert your design to real code. Also, you may have a separate team of programmers implementing your system. These team will likely be working from your original design documents. The clearer your intent is in your design, the better the final implementation will be. This is especially important if you are working with a global team, whose first language is not the same language you use.

Consistent Naming Convention

The first principle of selecting names in your database is to be consistent. Using consistent terminology, you can easily identify duplicate data items, preventing future design problems. Your consistent names should be descriptive and short. Try not to use special characters in your names (like dollar signs and dashes). Instead stick to alphabetic characters. In fact, it’s also a good idea to avoid numbers in object names. The reason for a number may be obvious to you, but there is a good chance that a 3rd party will not understand the significance of a number. It’s also important to note that some databases may even require you to use a specific naming convention in specific instances – for example in SQL identifiers must begin with letters.

Wherever possible you should also use short full words (50 characters or less is preferred). If you are using a verb, use the present tense. If you decide to use an abbreviation, make sure that it’s a commonly understood abbreviation. You also shouldn’t use redundant prefixes (for example “table_” before every table). Whether or not something is a table will be obvious from context. Always try and use the most easily understood version of a label or name, as this will make your design and intent much clearer.

Also, if you would like to separate words in a name, you should use a consistent method. Some methods you could try are:

You shouldn’t use an “_” character in one place and mixed (or snake) case in another. Also, some databases stipulate certain naming conventions. For example, in Postgres, you need to use Camel Case names. While it’s a personal preference, if there are no other constraints, we prefer the underscore method, as it makes intent less ambiguous.

If you stick to these rules, database users can easily understand the different relationships between items in your databases. For example, if run a school and you always refer to people in your database as “student”, anywhere student is mentioned you will know exactly what that means. But, if you referred to a student as a pupil in some tables and a student in another, you might not realize you were referring to the same element in both places.

A final suggestion is to avoid using names that reflect a specific application. Instead try and select your names based on concepts. An application name may change, but the concept behind the application won’t.

Singular or Plural names

When you are selecting names, you may be tempted to use plural versions in things like tables. After all, if you are tracking students, it may be natural to refer to a table containing multiple students as the students’ table. However, industry best practices are to typically stay with a singular version of a word in all instances. This helps to keep everything consistent in your database. It also avoids confusion in a language like English where the plural of a word does not always have the same base as the singular version. For example, the plural of the word “mouse” is mice, not “mouses”. Also, some words don’t even have a plural version, like the word “water”. By sticking to singular versions of words, you don’t need to worry about any idiosyncrasies in language.

Avoid Reserved Words

All database systems like SQL server, PostgreSQL, MySQL and ORACLE use words to represent internal commands and directives. It’s never a good idea to use these words in your own names, and it can make queries and commands confusing. For example, SQL uses the world “user” to refer to a security principle. If you name something in your database “user”, at first glance it will be difficult to distinguish between the SQL term and your database element.

Data Export and Naming Conventions

Modern systems often exchange data with other systems. There is a good chance that any database you create will have an API interface, especially if it supports mobile devices or websites. You should always keep in mind that API’s have there own naming conventions, that may be dictated by the data formats they support (for example JSON). If your database will be used in conjunction with an API, it’s a good idea to keep the naming conventions in the API and the database the same. Therefore, you should look at entire picture of your system, before you decide on a naming convention.

Tracker Ten Field Naming

Using our windows desktop Tracker Ten database system you can select names for all fields by simply double clicking the field names. You can change names at any time – our system till take care of all needed updates in the background.