Database Object Naming Standards
Part III: Key Columns
Having covered the basic naming conventions for tables and columns, I’m now going to cover standards for naming primary and foreign key columns in a table. For those of you who thought Part I and Part II were, well — a little pedestrian — stay tuned for a real magic show in this post. Or at least that will be true if you lead a pretty sheltered life.
By definition, a foreign key involves two tables: the table that is referring to information in another table, and the table that is being referenced. I will simply call the former the referring table, and the latter the referenced table, in the rules below.
Primary Key Column Naming Rules
id or uid Every table has a primary key, and it’s always the first column of the table. This breaks the alphabetical sequence rule described in a previous post, but it seems like a reasonable exception given the importance of this column. To make things even easier, there are only two possibilities when it comes to the naming the column; use either id or the uid. The former is always INT type, and the latter always whatever the particular database you’re using specifies a uuid datatype. The assumption is columns are virtually zero-cost, so why not have a column that has no other job than to uniquely identity each record. This is better than cluttering up the identification role with some other kind of business information.
Which One? If you know — really know — that the table created will serve as an absolute and definitive reference for the rest of your application, regardless of time and space, then you use id. However, use uid if you feel there is a possibility of two or more copies of the table structure will exist simultaneously, each populated independently of each other. Furthermore, if the possibility exists these two (or more) copies will be merged in the future, that seals the deal, it’s uid. So just use uid, and you’ll be covered in the event you want to merge tables in the future. The choice between the two types of primary keys, therefore, is based on whether a given table is a likely candidate for merge replication, or not.
Foreign Key Column Naming Rules
Primary Key of Referenced Table The first part of the foreign key is taken from the name of the primary key in the referenced table. So, for example, if Dog references Breed, and id is the primary key of Breed, then the foreign key starts with id. But there may already be one of those in the Dog table, and the ambiguity must be resolved, so….
Add The Referenced Table Name The referenced table, in the example started in the previous point, is Breed. That is going to be the second part of the foreign key name. But it’s not quite complete yet, two components need to be put together in some way. To do that…
Separate with Underscore Previously, I lambasted the use of underscores or other characters when naming database objects. Foreign keys are the big, whopping exception to that rule. I arrived at this rule by process of elimination. If both your primary and foreign keys in a given table are id, you’ve got a conflict right off the top. One option was to prefix id with the name of the table, such as breedId. Not bad, but it seems too much like the other, information-oriented columns in the table. So, how about IdBreed? Yes, but it seems to be in conflict with the idea that qualifiers are always prefixes, rather than suffixes. So, it’s the underscore character, resulting in the finished name Dog.id_Breed. I thought this particularly appropriate because the underscore really does connote the notion of separation — a visual metaphor for the data in the referenced table. You’ll find that they really jump out of the definition at you when reviewing table structures in the future.
Place at End of Table Foreign keys are always found at the end of the table definition. They appear in alphabetical order, based on the name of referenced table. So if you had three foreign keys id_Breed, id_Colour and id_Association, they would appear in the table definition as follows;
Dog.id_Association
Dog.id_Breed
Dog.id_ColourAs with the primary key, this breaks the overall alphabetical sequence rules spelled out in the basic table naming conventions, but I’ve found it’s valuable to have the foreign keys located in one region of the table definition, and away from the primary key, so the end it is.
Other Ideas
Creating the Index These posts cover naming standards. It’s not intended to be a general, ‘how to’ on creating indexes, but I think it’s worth pointing out that simply creating a compliant name doesn’t necessaily create the index. You often have to do that as a separate step which will vary a little depending on which specific relational database you are using.
Performance of uid Keys I’ve heard the argument that uids as a primary key are a bad idea for ‘performance reasons’. The admittedly limited testing I have done in this regard indicates there is not much of an impact. I would speculate that if you’re manipulating millions of records that there may be a measureable impact, but in the record volumes that most deal with regularly, you’ll be hard pressed to see the difference, I’m sure. This a subject I’ll delve into in more detail in a future post.
’Pseudo’ Key Columns I’ve tried to make the case that identification of records — which, after all, is the whole raison d’etre for keys — should not be diluted by adding any other meaning to them. There are going to be columns, however, that are going to be more verbose in nature and will still have a role in uniquely identifying records. The best example is the nm column which will feature in many tables. The important point is that any column that should be unique in the context of your application should be enforced by putting a unique index on that column. The same applies for compound keys. If a combination of columns is unique in the context of the table and application, then a unique index should be put on those columns.
That’s it, for this post. You can take your database design ideas a long ways with table, column and key naming rules, but there’s lots more coming up. Thank you very much for reading, and let me know what you think. Next up Part IV: createUdt and modifyUdt.
©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.
