Database Object Naming Standards
Introduction
Physical database objects are often treated likely the shirttail cousins of modern, object-oriented software development. How often have you peaked below the covers of a seemingly well-designed and well-constructed application, and been appalled and totally baffled by the underlying physical database structure, with its mish-mash of CustInfoTbl, GENERALINFO and STUD_MASTER (as in student master) tables. Some would argue this nastiness can be wrapped up in a data access layer, and the underlying physical structure never seen again. But the data in your application has to be physically stored somewhere sooner or later, so you’re likely to run into the structure of that data at some point. When you do, you’ll have to hope the person who created it — maybe even you? — followed some simple and predictable standard so that you can quickly and effectively unravel its mysteries.
In addition, just about every database encourages the incorporation of an increasing amount of program logic right into the database itself. Stored procedures, scalar and table-valued functions, views and other objects — with their increasingly sophisticated programming constructs — means more and more of what we traditionally expect to find in the application layer(s) will be done at the database level using tight, journalized transactions. Further evidence of this trend is the fact that you can now write your stored procedures in a variety of traditional languages such a C#, Python and others. Poorly named physical structure in your database will guarantee that your increasingly complex stored procedures will be ugly and obtuse, as well.
In the past, there were some pretty good excuses for having a lousy set of names for your physical data. Even some relatively modern databases placed numerous restrictions around the length of name and characteristics that could be used. In other cases, physical structure was inherited from legacy systems that had even more draconian naming rules. In the interest of expediency, the old structure names were simply brought into the new database software without change. Sadly, I have had the personal misfortune of seeing COBOL table structures adopted carte blanche into database tables — for goodness sake, what was the point of that? Furthermore, modifications to the structure may have caused even more damage over time. But a modern database tool not only has a liberal and flexible policy around naming of structures, it usually provides some fairly good tools for modifying them over time. Hence, there is no longer any good excuse for substandard naming of database structures.
If you need still more convincing, experience has shown that decisions around database object names tend to subconciously influence higher level structures. In other words, a poorly named table will tend to lead to a poorly named class in the data access layer, which may lead to poorly named application that employs that class. I know in the object-oriented world that it really shouldn’t work that way, but it likely happens more often that most care to admit. Clearly named physical structure encourages clear-headed thinking about the rest of the application. Also, with ever-improving hardware and network performance, I see a future where the logical and physical structure of the data are almost the same. At the very least, they will start out the same, and only grow apart to address specific issues of performance and functionality.
I have iterated through a series of naming standards for database objects over the years, and I finally stabilized on one particular approach, which I now follow with little variation. In the next and subsequent series of posts, I will attempt to methodically document the standard that I now use when creating physical database structure. It’s hard to tell how long the series will run, but over the course of it, I hope to be able to address most aspects of physical design; tables and columns will constitute the first couple of posts, then procedures and functions, and then beyond that, I’m not quite sure. I’ll just keep going until I run out of valuable things to say on the subject.
Of course, I encourage you, the reader, to contribute your thoughts, and I’ll do my best to respond to those in this series of posts. At the very least, tell me that I’ve got it all wrong, and I’ll do my best to further explain my logic. If you present a better idea, I will certainly incorporate that thinking into the ideas presented. First up, Part I: Tables. Until then, thanks for reading.
©2019 Intellog Inc.
This series of articles was originally published by Terence C. Gannon 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.
