Zen and the Art of Naming Objects
As a practicing database architect, I move around a lot–typically coming to a company that has a ‘broken’ system with the task of making it happy again. In the process, I redesign and rename objects so that the NEXT poor schmuck that has to maintain the system can do so with a minimum of pain. To that end, I’ve used a number of different naming conventions, depending on the circumstances. If I’m going to have the opportunity to train the DBAs, I’ll choose something a little more complex than if the next guy is just going to come in cold. Remember that NO convention is any good if you can’t get the day-to-day object owners to buy into it. That means clear communication and, if possible, help from them–don’t just present your DBA with your convention and expect them to follow it. Get them to write some of it themselves, while keeping an eye on the utility and viability of what they create.
Following is the schema that I currently use. I personally wrote about a quarter of it, with the rest being either pre-built or the result of team discussion.
Databases: <applicationid><specificdbid>_<usage><release>
Applicationid is a four character code identifying the application that is the owner of the database. I’m not generally a fan of massive Frankenstein databases that fill every need. Some applications that we use are Site Traffic Reporting (strf), Commissions (cmsn) and Data Administration (dadm).
Specificdbid is a four character code identifying the purpose of the database within the application. ‘Main’, ‘Lkup’, and ‘Whse’ are some specific ids.
Usage is a one character lower case code identifying how the db is used. Some examples are ‘t’ for test, ‘p’ for production, ‘x’ for a throwaway/work database, etc.
Release is a one digit incrementing numeric key (1,2,3,4…) which identifies the number of major revisions which have occurred. Before you even say it, I haven’t seen a database go through more than 9 major revisions without becoming something different. If you like, add another digit. Or not.
So strfMain_p1 is the first revision production database for site traffic reporting. StrfWhse_t2 is the second revision test database for data warehousing on the same application.
Tables: <prefix><tableident>_<body>
Prefix is a lower case single character which identifies the subsystem involved. Examples: ‘a’ for a primary application table, ‘l’ for a lookup, ‘t’ for temporary.
Tableident is a three character uppercase code which is mnemonic to the body of the table name. In the case of a temporary table (one that has a ‘t’ prefix), I’d recommend that this identifier be the initials of the creator.
Body is a mixed case, variable length descriptor of the table’s purpose, involving one or more words. You should avoid words like ‘table’, ‘key’, ‘value’, or any other redundant and/or useless identifier.
Some of my tables are aJSM_JobSearchMonitor (an application table that monitors the job search process) and lCST_CustomerType (a lookup table of customer types).
Columns: <tableident>_<type><body><qualifier>
Tableident is the same as the table in which it resides. This seems redundant on the surface, but is actually quite useful when referencing similar columns in multiple tables. At a glance, you’ll know where your data is coming from.
Type is a two or three character code which identifies the functional usage of the column, NOT it’s datatype (though in our system, certain types tend to be of specific datatypes). Examples are ‘qty’ for quantity, ‘cd’ for identifying code (which may be a key in another table), etc.
Body: Similar to the body of the table, but now we’re describing the column.
Qualifier: Not always necessary, but sometimes used to avoid confusion if you have a couple of similar fields. We use qualifiers like ‘First’, ‘Max’, and ‘Dflt’
Some example column names are: lQTP_cdQuotaType, aQLP_qtyQuotaLimitMax, and aNTT_dtUpdatePrev.
Stored Procedures: <prefix>_<body>
Prefix is a two character identifier of the basic purpose of the procedure. Avoid using sp, both to functionally separate system stored procedures and to avoid the really tiny speed decrease that happens when you do that. Some prefixes I use are ‘qp’ for querying (non-update) application sps, ‘bp’ for sps that build other data (such as aggregates for multi-dimensional processing), and ‘up’ for an application proc that updates data.
Body is just the description, as I’ve already discussed. If you drive an external report application, let me suggest that the body of the sp be the same name as the report–trust me, you’ll thank me later.
Views: <prefix><primarytablename><qualifier>
Prefix, in my case, is always ‘v’. This allows you to quickly tell the difference between a view and a table in a query. Primary table name is–guess what–the primary table used in the view. This works for us, since our views are generally just single table limitations for security or web page parameter values.
Qualifier is the same as for columns.
Indexes: <prefix>_<body>
Nothing complex here. PK for primary key, FK if you feel the dubious need to have foreign keys at all, CL for clustered, etc. Body is the same as before.
Some final thoughts–I’m not a big fan of enforcing DRI through the database, so I’m not going to talk about relationships. The key to all of the above is to be consistent–use what you want, but keep it the same across the board. If you don’t like what I’ve laid out above, by all means feel free to modify parts of it or just use something else. In the end result, it’s all about making lives easier–yours, your team’s, and whoever comes later.
As always, I appreciate any comments/questions and will respond to anything. I’m seeing a therapist about that. That, and the smiting thing.
