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.