Database Object Naming Standards
Part II: Columns
In Part I: Tables, I discussed standards as they relate to naming tables. This time, it’s:
Column Naming Rules
Base Names The concepts modeled with database tables tend to be described in similar terms. Many tables contain a column with descriptive information, a column with a name or a column with amounts, for example. For these, it’s useful to have a list of standardized column names used repeatedly. These are referred to as Column Base Names, or just Base Names. The most important concept is that the table in which the Base Name is found provides a context. If you are using one of the Base Names by itself, as will often be the case, it is inferred that the thing being described with the dsc Base Name or named with the nm Base Name is the idea being modeled in the table in which these columns are found. More to the point, it’s not necessary to repeat some portion of the table name itself within the column name. For example, Whale.nm (think ‘whale name’) reads a lot better than Whale.whaleNm (think ‘whale whale name’). Nothing against whales — in fact, some of my best friends are whales — but the second whale in the identification of this column adds nothing in terms of clarity.
Elimination of Redundancy Of course, there are many situations where there is a requirement for multiple instances of a given Base Name within a table. In the table Dog, for example, it’s reasonable to expect a nm column; Dog.nm. However, let’s assume the application design evolves and the dog’s formal show name needs to be captured. A column with the qualifying prefix show would be added to the column name; hence showNm. As a result, the Dog table contains Dog.nm and Dog.showNm. It’s also tempting to rename the existing nm column to nickNm or familiarNm. However, the two names don’t have equal status. For example, if you take your champion Weimaraner out for a scrape, you are not likely to say; “not there, Hampson Whiskeyman of Foucalt!”, but rather, the less pretentious and slightly less definitive “not there, Spot!” The latter is the name you regularly use, therefore it makes sense to use the Base Name for the familiar name of the dog in question. It’s sort of like a ‘default’ name. There are situations, however, where names have absolutely equal status; Person.firstNm and Person.lastNm is a classic example. Unless you are Oprah, Cher or Madonna, you need both names (at least) to fully identify a given instance of Person. In this case, it’s important to eliminate the Base Name from the table. This reflects the fact that in this kind of situation, all nm columns have equal status. The presence — or absence — of the Base Name implicitly tells you something about the structure of the data being modeled.
Capitalization The first letter in the column name is lowercase, which is the opposite of table names described previously. If tables are the database analogue of class, then columns are the database analogue of class properties. I come from the school of thinking that the first letter of a property name is lowercase, to distinguish them from class names. So, Person.lastNm, never Person.LastNm. Similar to tables names, use camel case to distinguish the various parts of the column name, never underscores, spaces or some other character.
AbbreviationsAs with all names for database objects, do not abbreviate for all but the most self-evident ideas. The Base Names described above are an obvious exception to this rule. Due to their very frequent use, however Column Base Names become part of the visual furniture. You will see dsc, but in your mind, you will hear the verbose ‘description’. In all other cases, no abbrevations — the argument for which was spelled out in the rules around naming tables.
Alphabetical Order Columns always appear in alphabetical order. Alphabetical order is useful if you have a particularly long collection of columns in a table. You can glance at the structure and determine if a particular column exists or not. The significant exception to this rule is both primary and foreign keys, to be dealt with in a future post.
Summary Example
The following continues the example of the Dog table that I started above, and it applies all of the above rules;
Dog.birthDt
Dog.dsc
Dog.firstShowDt
Dog.heightAmt
Dog.lbl
Dog.mostRecentShowDt
Dog.nm
Dog.showNm
Dog.weightAmtKeep in mind that there are a few other rules that I have not yet covered that will impact the table structure, but I will add those items to this basic table structure as and when they are covered in future posts.
Other Thoughts
When JOINing two tables that use the standards described above, you will have to get used to using the fully-qualified column name. This is because there is a pretty good likelihood your JOIN will expose two column names that are the same. For example:
SELECT DISTINCT
nm
FROM
Job INNER JOIN
Role ON Role.id_Person = Person.idwill likely fail, because there is a pretty good chance that both Job and Role will have a nm column associated with them. Therefore, the syntax must read:
SELECT DISTINCT
Job.nm
,Role.nm
FROM
Job INNER JOIN
Role On Role.id_Person = Person.idAt first, this would appear to be a significant drawback, but I believe in using fully-qualified columns names, anyway, for the purposes of making the SQL statement more readable. The example above also provides a sneak peak at standards for primary and foreign keys, but these are covered in more detail in Part III of this series.
Conclusion
There are quite a few other rules that I apply to the naming of columns in database tables, but I will leave those idea for a future post. Thank you very much for reading, and let me know what you think!
©2019 Intellog Inc.
This series of articles was originally published by TerenceCGannon in 2007 in a primordial version of The Intellog Blog. As we undertake a brand new, clean sheet database design for a new client, we decided to go back to these source documents to see if they have stood the test of time. For the most part, they have, but we also took the opportunity to update them a little for a more modern era. We would love to hear your feedback.
