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?

You may also like...

2 Responses

  1. Raul Baron says:

    Hi Ember and congrats for your great blog.
    We agree to use the exact same standards as you mention, i.e. FKs for Foreign Keys, IX for indexes, TB for tables, UQ for Unique constraints, VW for views, etc. As you say it is an excellent practice to know which entity is responsible for a certain problem.
    As for database names our imagination declines since we use suffixes such as db2pro for production or pre for pre-production or test for test environments. We waste 3 positions (db2) since we only have DB2 in this customer, so there’s no need to say it’s a DB2 database πŸ™‚
    And as the instance name concerns, our creativity goes even worse. We use one server-one instance-one database for each environment but test and development (one instance and two databases). Again, no actual need to name instances other than the factory name. On the other hand we only have five databases to maintain for now.

    As to the old application (cobol + z/Linux DB2) it only has tables and indexes and the naming standard is very curious: each table name has a prefix of 4 chars indicating the business area plus 4 more chars that try to summarize what it is about. e.g. PARMUSUA means parametric table of users. Indexes are named just like the table but the 4th char is a number indicating only the index number. So you never know how many indexes a table has nor what is that index about. e.g. PAR3USUA. This is because we evolved from DB2/VM (that is, DB2 natively on z/VM) which had a table and/or index limitation of 8 chars. When we migrated into DB2 LUW we lazily kept that standard inherited from the z/VM period.

    I hope not to have written much too long πŸ™‚

    Best regards from Madrid

  2. Pavan Kristipati says:

    Ember — This is great. Co-incidentally I am used to following very similar naming standards. I use PK_ as a prefix for all Primary Key Index and U_ for Unique key index.

    Sometimes, if the application allows, I also would like to enforce environment name within the schema name. For example, a stage table in a DW database could be EDWSTGPR.. This naming convention tells me that this is a EDW database, stage schema and Production environment. The last two letters differ from environment to environment.. viz., QA, DV, IN etc.
    Best Regards.
    Pavan Kristipati.

Leave a Reply

Your email address will not be published. Required fields are marked *