Relational Database Design - Phase 3: Naming Fields and Tables
Updated: Jul 2, 2020
This post is part of the Database Design series that I’ve been posting. (Please see the bottom of this post for links to all my posts in the database series.)
Field and Table naming should be sorted out in the design phase of your Database. This is because if you make changes later far more work is involved since the names will need to be changed in every place they had been used. If this is not done thoroughly inconsistencies will arise leading to errors or unexpected results.
Although Field and Table naming is a critical part of Database Design, the recommendations below also apply to the naming of all variables used in computing.
(As an aside, the first computer system I worked on was SAP which is an extremely popular enterprise-wide system especially for large companies.
It is a system designed in Germany. I started working with it when it was still in its first few releases. At this stage, it was still a mainframe system and all the variable names were 5 characters long and based on German words. I can still remember that the variable name for “company” was “BUKRS” and “plant” was “WERKS”. This certainly added another level of complexity to an already complicated job.)
The goal when naming Database Fields and Tables (and actually all variables in programming) is to:·
Make them understandable·
Make them unique·
Make sure that the computer won’t think it is one of the programming languages reserved words. (A reserved word is a word that a database or programming language uses as one of its commands e.g. “Date” is used across many languages to convert a number into a date format. Even if this is not the case with the code you are currently working on it is possible that your program might link to a database or system in another language later where it could be a reserved word.)
Here are some recommended guidelines for naming database fields, tables, and programming variables.
It is a good idea to decide on the rules you will adhere to before starting any project. Changing names can be very complicated the further you move into a project and may quite possibly cause something that was working to crash. You will then need to spend time fixing it up again.
As noted in my SAP example above, it makes life needlessly complicated if your field names are meaningless.
For example, calling an “Invoice Date” something like “iDate” is not very meaningful. “invoiceDate” would certainly remove uncertainty but programmers are essentially lazy (well I am at least) so I would call the field “invDate”.
Errors can arise if you use a reserved word (a word that the database or programming language uses as a command such as “date” or “file”.) These errors can be very hard to identify leading to wasted time and frustration. The use of more than one word for your field names makes such errors far less likely.
“firstName” instead of “name”
“arrivalDate” instead of “date”
How did camels get into this discussion?
Camel Case is a format for naming fields where the first word(or abbreviated word) starts with a lower case letter and then each following word starts capital letter. Here are some examples:
This helps us in a couple of ways:
It is obvious that the “word” (field name) is something that we have thought up. Databases and Programming languages typically have all their commands in uppercase or lower case. This means that you can easily differentiate when looking through the code what is a field or variable name and what is a command.
Having your field names all in one case makes it difficult to see where the next part of the field name starts. For example:
dateofbirth or DATEOFBIRTH are hard to read and quickly understand. With dateOfBirth it is easy to see there is a new word “Of” and “Birth”
This is a diagram of this naming convention which shows why it is called Camel Case.
In setting up a database we need to come up with names for our Tables. If we use the identical naming structure that we use for Fields, confusion can arise as to whether we are looking at a Table name or Field name. Remember – any confusion results in wasted time and frustration.
Therefore, it is sensible to start Table names with the letters “tbl”. We immediately know what we are looking at refers to a Table. For example:
Typically, we don’t follow this approach with Fields since they occur so frequently. The assumption is that if “tbl” is not before a name then it is referring to a Field.
This approach is also helpful in programming. Using “arr” before Array names. “f” before file names.
! @ # $ % ^ & * ( ) _ - + = " ' : ; ?
When I first set up my personal Gmail address, I found that my name had already been used so Gmail suggested other options that included numbers, etc. I didn’t like any of the proposals, so I decided to use an underscore “_”. I made my Gmail address
“email@example.com”. Bad decision! When telling somebody my email address very few people know what an underscore is and where to find it on the keyboard. The underscore can be confused with a space or a dash. This frequently occurs when filling in a form.
The same sort of problems arise if you use an underscore (or any other special character like @ or $ or ^.) Examples of fields using special characters would be:
Some programming languages won’t accept Field/Variable names with special characters include. The Database you are setting up might accept special characters but then the programming language might not. Best to avoid special characters totally.
Please write any advice you have for naming Fields and Tables in the comments section below to assist others (and me.)
As promised, here are the links to all the posts in my Database Series:
You pay for it - are you using it? Microsoft Access pointed out that there are circumstances where Microsoft Access was able to handle the system requirements in a far easier and more professional way than Microsoft Excel.
Making Sense of the Relational Database Structure is an infographic which shows the relational database terms and how they interrelate. If you are unfamiliar with these terms, this is a useful resource to look at as we go along.
Relational Database Design – Phase 1: Rough provided 2 steps to easily brainstorm the initial concept of what you were thinking of for your database.
Relational Database Design – Phase 2: Refine explains how to turn your rough concept into a Relational Database Structure. The process is known as Database Normalization.