Database Object Naming Standards
Part I: Tables
This is the first part in a series of posts on database object naming standards. In the previously posted Introduction, I laid out the case for the value of using these naming standards in general, and in this post, I talk specifically about table names.
Table Naming Rules
Singular Table names are always singular; Goose rather tan Geese, Crow as opposed to Crows, and Whale, not Whales. Simple enough. However, by saying singular, that does not preclude collective terms. So Gaggle, Murder and Pod would all be perfectly good table names, whereas Gaggles, Murders and Pods would not. To understand why, it helps to think about the columns that will eventually populate this table, and in particular, how the combination of table name and column name will sound when read or discussed. I would argue that Crow.nm (think ‘crow name’) and Crow.dsc (‘crow description’), make more sense than the plural equivalents (‘crows name’ and ‘crows description’). Or, stated another way, the name and description columns relate to a particular crow in one row of the table, not the entire population of crows in all the rows of the table.
One Noun I strive for one noun that best describes the collection of data the table contains — the simpler the name, the more self-evident the table and its content becomes. Car would be a good table name, whereas CarFender would not. There can’t be much confusion as to what the Car table is likely to contain. CarFender might contain information about cars, or fenders or maybe somebody who ends carfs (whatever they are), or possibly all three. Just looking at the latter makes me want to split it into separate tables with some sort of relationship with each other. If you’re really stuck for good, unique name, try plugging some possible names into a thesaurus, and see what synonyms exist. You may be pleasantly surprised at what you find. There is one major exception to the One Noun rule, described in Associative Tables, below.
Capitalize First Letter The first letter of the table name is always capitalized. I took a queue from object-oriented practices in this regard; tables seems like the database analogue of class, and the records within the table seem like instances of the class. By convention, the first letter of a class name is capitalized, and hence, the database table name should follow that convention. If it’s simply not possible to follow the One Noun rule noted immediately above, then use the so-called camel case to distinguish the words within the name. Don’t resort to underscores, or worse yet, spaces in tables names to separate words — it’s just a waste of screen real estate, and does nothing that camel case doesn’t accomplish just as well.
Elimination of Redundancy If you really work at applying the One Noun rule, it’s just a matter of time before there’s a situation where the same, simple noun describes at least two completely different ideas. Point is a good example; it could represent a physical location on the surface of the earth, or a single bullet on a presentation slide, or maybe the sharp end of a pencil. In some cases, the overlap may be eliminated by the use of schemas (more on that in a future post), but for the time being, this is the one situation where a single, clarifying adjective prefixed to the noun is in order. GeographicPoint and TalkingPoint will create the necessary distinction between the two ideas. However, avoid preemptive use of this technique; “I had better name the table GeographicPoint just in case another type of point comes along in the future.” Wait until you actually have an overlap, then deal with it, rather than trying to imagine all of the possible future situations — you’ll just drive yourself crazy and in the interim, degrade the simplicity and clarity of your table names.
Associative Tables These tables resolve many-to-many relationships that exist in your data structure. Recently, I have started breaking — er, evolving — the first, One Noun, rule because I came to the conclusion associative tables really are fundamentally different from other tables. They describe a relationship, not a thing. Hence in their case, I would use — horrors! — a transitive verb. For example, the tables Presentation and Document are related in the sense that a given presentation could consist of many documents, and a given document could be used in many presentations. This is a classic many-to-many relationship. The associative table that resolves the relationship could be the transitive verb Contain. Think ‘Document-Contain-Presentation’ and ‘Presentation-Contain-Document’. I’m of mixed opinion about the use of the singular in this case. I like the absolute nature of the Singular rule, but use of the plural does make it read better. So let’s go with singular, for the purposes of consistency, at least for now.
No Abbreviations For all but the most completely self-evident ideas, abbreviations should be avoided. Just about any reason to use ultra-short tables names vaporized with the advent of near-zero-cost data storage and the various Intellisense-like development systems available today. Let’s face it, you’re just not typing the entire table name all that often. You will read it frequently, however, so what you lose in brevity is made up by the immense value of your code being understandable by yourself and others, now and years in the future.
Other Thoughts
Since early 2005, I have either led or participated in software development projects using the Agile methodology. Primarily due to time constraints, only the tables that were actually needed during the then-current iteration were created. Even if you’re not following Agile, it is strongly recommended that you create only the tables demanded by specific requirements you clearly understand. To borrow from and butcher von Moltke a little; “A design never survives first contact with the user.” What you learn in each iteration of development may well eliminate tables you were sure you needed. At the same time, you will be required to create tables you previously never even imagined. Let specific, current, feature-oriented requirements drive out structure — always.
If you are successful in applying all of the rules above, you should be able to look at the list of tables and have a pretty good sense of the structure, without having to resort to system documentation that may (or more likely, may not) exist. In fact, that’s the litmus test — if you look at the list of table names and you can’t more-or-less figure out what the structure is, then you’ve probably got some more work to do.
You may conclude that following all of the rules above very narrowly defines the possibilities; two people, separated by the time and space but looking at the same subject matter, might very well come up with very similar or even identical table names. For example, if one of entities that is being described is a human being, exactly how many different words are there? Eliminating profanity, pejoratives and the politically incorrect ones, not that many, really. So why does everybody keep creating the very same Person table from scratch? Good question, for which I can’t currently think of a good answer — the subject of a future post, possibly?
I will not attempt to address the question of logical vs. physical modelling, or the mapping of entities to physical objects — I may in a future post. What’s certain, though, is that sooner or later the data winds up in a table structure within a relational database, and I assert that a little care and attention to its name will pay off over the long term.
Next up, Part II: Columns which covers naming standards for database columns. Thank you for reading, and please let me know what you think!
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.
