Image for post
Image for post
A Colossus code-breaking computer as it was during World War II. (image: Wikimedia)

Database Object Naming Standards

Part II: Columns

Intellog Inc.
Aug 19, 2019 · 5 min read

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; “”, but rather, the less pretentious and slightly less definitive “” The latter is the name you regularly , 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.weightAmt

Keep 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.id

will 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.id

At 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.

The Intellog Blog

The Intellog Blog

Digital Content | Social Media | Digital Project…

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store