DB2 Naming Standards

Last week, I read this article by Craig Mullins: https://www.sswug.org/craigmullins/sql-server/whats-in-a-name-on-database-naming-standards/. Go read it, this blog entry will still be here when you get back.

I have to say that in large part, I agree. I don’t like having differing naming standards for tables and views. And certainly not for aliases.

I do like to have a naming standard for constraints and indexes though. For constraints, I’m never going to change one constraint to another – a primary key to a foreign key for example. So I like to include PK and the table name in the constraint name for primary keys, and FK and the table name in the constraint name for foreign keys. This makes troubleshooting easier, because when an insert or update fails due to a FK violation, many applications will give the foreign key name. I like it when that name means something to me. And again, I’m not going to change the meaning of an existing FK constraint, so there’s no reason not to include that information in my mind.

I get Craig’s contention about keeping the meta data out of the object name, but with indexes, there’s very little that can be done to alter the index – significant alterations require dropping and re-creating anyway, where re-naming is easy. I agree in not including PK or FK in an index name, as the primary or foreign keys can change without regards to the index name. But I do like to have ‘IX’ in there for indexes and a ‘U’ if it’s a unique index. I’m not going to change an index from unique to non-unique without the opportunity to rename it. One of the reasons I have IX in there for my index names is to differentiate them from vendor indexes or to meet with vendor naming standards.

In IBM Websphere Commerce databases, there are very specific naming standards that custom objects should conform to. They are documented on a page in the IBM WebSphere Commerce Information Center.

Like it or not, if you have a vended database, you have to conform to vendor standards. Using those standards and a knowledge of IBM WebSphere Commerce databases, I have a pretty good idea just from the index name whether it is a primary key index, a base vendor index or a custom added index, which can be useful when reading explain plans.

I also have my own naming standards for both DB2 instances and databases. Instances, I like to have very generic names because I see them re-used for various purposes in both test and production environments. But databases themselves I like to have identifiers in for the app (WCS, Sterling, WebSphere Portal or whatever), for the client (several numbers or letters) and an identifier for the environment (Prod, stage, QA, dev, etc). That’s a lot to fit into 8 characters, but I find having these really makes a difference in preventing busy dbas from accidentally doing something to the wrong client, environment, or database. I also tend to have a database nearly exclusively dedicated to one application – a luxury that I know not everyone has.

A pet peeve of mine is also in column naming within tables. I get so frustrated when developers come to me with a table with columns named “field1”, “field2”, and “field3”. In today’s RDBMS it’s not hard to add a column later if needed, and I get a lot of developers submitting tables to me with these “just in case I need them” columns. Each field should have a descriptive name, and columns should not exist just in case they are needed at some future time – talk about a waste of space and a potential performance issue if all of those fields are suddenly populated and create overflow records.

And of course, I strongly object to anything named in mixed case or named with a space in it in a DB2 database. Those make scripting maintenance difficult. Most annoying thing I’ve seen along these lines is a Tivoli database that I supported for a while that had a trailing space on the schema name. That was insanely difficult to deal with in my runstats and reorg scripts.

So what naming standards do you have? What are your naming standard pet peeves?