Database Object Naming Standards
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
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
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_Association, they would appear in the table definition as follows;
As 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.
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.
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.