Database Naming Conventions
Database Design Fundamentals
Naming conventions are critical in programming and database design as it prevent early death, either by a co-worker or self-inflicted. Giving the necessary attention to naming things appropriately will save you major headaches when it comes to understanding and maintaining code.
Terms such as Foo and Bar (which are used in a gazillion coding examples and tutorials) are meaningless as they have no context and you will not find any of them in my writings except in this sentence. Name things and what they are. If it is an employee, call it Employee and not Emp. Call it Customer and not Cust.
That said, sometimes it is okay to use acronyms. For example, it is better to use URL instead of Uniform Resource Locator as the term URL is well known. The same goes for terms like HTML and XML. But, if in doubt, write it out!
It is not only important that things should have proper names. It is also important that we use the correct format.
For the rest of this post, we will build a Projects database consisting of Projects and Team Members and Assigned Projects. So we need to create three tables, Projects, Team Members and Project Team Members (or Team Member Projects).
Let's deal with spaces first. There is no space for spaces when naming stuff. Let me repeat this. There is no space for spaces when naming stuff. Spaces cause all sorts of problems. Most programming languages and databases will not allow the use of spaces. But even when naming folders, please do not use spaces.
Singular vs Plural
Shall we name our tables:
Project or Projects?
Team Member or Team Members?
Project Team Member or Project Team Members?
Team Member Project or Team Member Projects?
Use either singular or plural names but, please, do not mix them. Be consistent! It is very frustrating when you work with databases containing hundreds of tables, and little to no attention is given when naming tables and columns. It wastes time and causes IT professionals to have unwanted criminal records.
I prefer to use singular names (for example 'project' and not 'projects') as it allows for consistency. See the Demo section below. Using different casings, let's name our three entities, projects, team members and assigned projects.
When using Pascal Case (derived from the Pascal Programming language), we capitalize each first letter of every word. So we will have:
Please note that I use singular and no spaces.
You could use plural which will result in:
When using Pascal Case (derived from camel humps), we capitalize each first letter of every word except for the first word. So we will have:
Using Kebab Case (derived from kebabs on a stick), words are lowercase separated by a dash. So we will have:
Using Snake Case (derived from a snake sailing on its belly), words are lowercase separated by an underscore. So we will have:
Using No Case (I made up the name), words are lowercase separated by nothing. So we will have:
Please do not do this. It is bad enough that this is a naming convention for Java packages. This very quickly becomes unreadable.
Which Casing to Use?
It all depends on what you are building and what technology you are using.
Kebab Case is preferred for URL names. Thus it is better to say: https://website/annual-sales than https://website/annualsales. In reality, you will see both versions. You will even encounter Snake Case, https://website/annual_sales. Again, be consistent!
Python, uses Snake Case where each word is lowercase and separated by an underscore (example: calculate_sales_tax).
When it comes to naming conventions for databases, I use Snake Case exclusively. That said, Microsoft is using Pascal Case in their sample AdventureWorks database and I do not necessarily have a problem with that. As long as you are consistent!
Here is how I will name the Project, Team Member and Project Team Member entities in the Projects database:
|id, first_name, last_name
The project table has two columns, 'id' and 'name'. The primary key is called 'id'. Some DBAs will use 'project_id' instead. I use project_id in the project_team_member as a foreign key though. The reason is, that if I see a name before the 'id' part, I know I am dealing with a foreign key. If I use the same name in both tables, it may not be so obvious.
Some DBAs go even further and prefix every column name with that of the table name. For example:
|team_member_id, team_member_first_name, team_member_last_name
I am not a fan of this. I like to be explicit when it comes to programming, but one can be too explicit. It's like saying round circle instead of just circle. The fact that it is a circle, implies the fact that it is round. Similarly, the fact that the id and name columns are in the project table implies that they belong to the project table. That said, when joining multiple tables in queries, the prefix example is nice to work with. You will see what I mean by this later in the course.
A quick detour! Something we have not addressed in the previous post, when we discussed keys, were composite keys. In our Project example above we have:
project_id column is a foreign key in the
project_team_member table referencing the
id column in the
project(id, name) table. Similarly, the
team_member_id column is a foreign key in the
project_team_member table referencing the
id column in the
team_member(id, first_name, last_name) table.
But, because the combination of
team_member_id is unique per row, we use these two columns as the primary key for the
|id [PK], name
|id [PK], first_name, last_name
|project_id [PK, FK], team_member_id [PK, FK]
In this post, we took a quick look at database naming conventions. What I discussed here is not exhaustive, but it's a good start.
Next stop, we download and install a Relational Database Management System and create our first database. See you there!