Good Database Design Part 2: Naming

Would you prefix your database name to indicate that it is a database? If so, you need to stop reading this and never call yourself a database administrator (preferably don’t even associate yourself with anything in the database realm and should probably never consider yourself a critical thinker, but I don’t want to get hurtful). I feel just as adamantly about prefixing tables to indicate that it is a table—‘tbl,’ ‘t,’ ‘t_’—it is horribly wrong. These are the base objects of a database. Hungarian notation is good. Note that you don’t see variables types prefixed; have you ever seen a variable named ‘TypIntFoo?’ Tables—no prefix. Ironically, the people committing this offense typically do not prefix their column names with data types. I would love some insight into that reasoning.

This is just my opinion, but I back it up with C.J. Date holding the same. As well, I have never seen a professionally designed database prefixing tables. I have seen commercial products that do not even meet first normal form, have a minimal semblance of design, that require escalated permissions to run; and they still are not prefixing tables with a ‘table’ indicator. Prefix (would ‘object typing’ be a better term?) your views if you must, stored procedures, what have you. Leave the tables alone.

Outside of that gripe, I do not care how you do it; just remain consistent throughout your model. Just found a second annoyance. I have seen databases where half is decently designed, and half uses a completely different naming convention (and often not so well designed). Stick to the initial design and naming convention. Cohesiveness facilitates maintenance. That includes using comment blocks of the initial development push (or changing them all to what you want). I recognize the world of IT is not the most team oriented (I am a misanthrope myself) but with the glory goes the blame.

What do I prefer? All objects named in sentence-case bearing full and descriptive names (which does not work so well with Oracle, unless double-quotes everywhere are appealing to you). Most DBMS’s today have generous limits on object names; take advantage of it. Try to keep the abbreviations to a minimum as they require knowledge of the business to determine meaning.

The only place I do not mind underscores are for contrived keys (TableName_UID (although TableNameID works too)) and for those rare situations when you have to rename a migrated column (for instance, FooID migrated twice to one table, one of them becomes Purpose_FooID). By using underscores like this, it serves as a visual cue that we are dealing with a physical element.

I prefer each relation to have a one or two word name (this leads to convenient understandable aliases). I also like grouping my ideas together by migrating part of the name:

ServerNetwork --- Server --- ServerBackup --- BackupVolume

It presents a nice way of physically reflecting the logical flow (we see two tables related to server, which is a base idea and two related to backups which relates to servers).

Table names should be singular. If you do not do that, it gets awkward quick. ‘Servers may be ok, ‘ServersNetworks’ is rather lame and ‘ServerNetworks’ is inconsistent in at least one sense. And what about a table named ‘Equity?’ I sometimes use plural forms for view names. When they are a bit denormalized, you are illustrating a larger idea and the plural form may capture that. Again, C.J. Date agrees. Relations are types and types are singular (regardless of what Oracle Designer defaults to).

If names have repeating elements, I often lead with the repeating bits, for better or worse (NameLast, NameFirst and DateStart, DateEnd).

For each model, try to incorporate the users’ terminology into your design, where appropriate. Do not force a name out of habit. This makes it easier on the report monkeys and shows your understanding of the business rules.

The important point is using a consistent naming convention throughout a model. Like most things, preference weighs in heavily. Do not be afraid of modifying your naming convention; it should evolve with your experience.