| Craig S. Mullins
Database Performance Management
In A Name
Craig S. Mullins
establishment and enforcing of naming conventions is often one of the
first duties to be tackled when implementing new software. Adequate thought and preparation is required in order for
such a task to be successful. What
amount of effort should be extended in the creation of appropriate
database naming standards? Are
current industry standards acceptable?
Shakespeare, many, many years ago, may have said it best when
"What's in a name?
That which we call a rose
by any other name would
smell as sweet."
if that is true, then why do those of us in the DP industry spend so
much time and effort developing and enforcing naming standards? Maybe what something is called is not quite so trivial a
matter after all!
we know what Shakespeare was trying to convey:
the name by which we call something has no affect upon the
actual object. Calling a desk a lamp will not turn
it into a lamp. It is
still a desk. Sometimes
DP professionals, and database developers in particular, forget this. This article will provide a novel approach to database naming
standards by ignoring all of the basic generalizations that were
foisted upon us in Database 101 class and taking a fresh look at the
naming of relational database objects.
The article will concentrate on DB2 objects, but the arguments
made in the article are just as valid for other relational databases
and Their Cousins
a relational database management system the primary data store is the
table. A table consists
of multiple rows, each with a fixed and unchanging number of defined
columns. However, most RDBMS provide alternate means of accessing data
from these tables. For
example, DB2 allows the following:
of these alternate means of access are similar in one way:
they all present data by means of values in rows and
columns. An end
user need not know whether he is querying a table, an alias, a
synonym, or a view. To
the user the results are the same – data represented by values
in rows and columns.
brings us to our first revision to the common wisdom for
database naming standards:
is common for most shops to implement different naming
conventions for each of these objects.
Does it really make sense for them to named differently?
Let's examine the pros and cons.
your current table naming conventions.
If your shop is typical, you will find a convention that
is similar to the one depicted in Figure 1.
If they are different, pause for a moment to ask yourself
why. This is almost
an industry standard for table naming.
You most surely do not want force every DB2 table to
begin with a T (or have a strategically embedded T within the
table name). The
name of a DB2 table should accurately and succinctly convey the
contents of the data it contains.
The naming convention displayed in Figure 1 accomplishes
1. Typical Table Naming Standard
this brings us to our second rule for revising database naming
most shops avoid embedding a "T" in table name, many
of these same shops do embed a character into alias, synonym,
and view names. The
primary reason given is that the character makes it easy to
determine what type of object is being accessed just by looking
at the name. There
are two reasons why this is a bad idea.
The first is a semantic reason; the second a flexibility
semantic terms, an object's name need only identify the object,
not the object's type. Consider
the following arguments:
How are people named?
Usually one can ascertain the gender of someone simply by
knowing their name but would you banish all males named Chris,
Pat, or Terry? Or
maybe all females named Chris, Pat, and Terry?
After all, men and women are different.
Shouldn't we make sure that all
men's names are differentiated from women's names? Maybe we should start all men's names with an M and all
women's names with a W? If
we did, we'd sure have a lot of Marks and Wendys, wouldn't we?
The point here is that context enables us to
differentiate men from women, when it is necessary.
The same can be said of database objects.
are COBOL program variables named?
Do you name your 01, 05, and 77 level variable names
differently in your COBOL programs?
For example, do all 01 levels start with "O"
(for one), all 05 levels start with "F", and
all 77 levels start with "S"?
No? Why not?
Isn't this the same as forcing views to start with V (or
having a strategically imbedded V within the name)?
What about the naming of pets?
Say I have a dog, a cat, and a bird.
Now, I wouldn't want to get them confused, so I'll make
sure that I start all of my dog names with a D, cat names with a
C, and bird names with a B.
So, I'll feed C_FELIX in the morning, take D_ROVER for a
walk after work, and make sure I cover B_TWEETY's cage before I
go to bed. Sound
ridiculous, doesn't it?
whole point of this tirade is that if we don't manufacture hokey
names in the real world, why would we want to do it with our DB2
objects? There is
really no reason to embed special characters into DB2 objects
names to differentiate them from one another.
It is very practical and desirable to name DB2 objects in
a consistent manner, but that consistent manner should be well
thought out and should utilize the system to its fullest
capacity wherever possible.
second reason for this rule is to increase flexibility. Say, for example, that we have a table that for some reason
is significantly altered, dropped, or renamed.
If views are not constrained by rigid naming conventions
requiring an embedded "V" in the name, then a view can
be constructed that resembles the way the table used to look.
Furthermore, this view can be given the same name as the
old table. This
increases system flexibility.
users don't care whether they are using a table, view, synonym,
or alias. They
simply want the data. And,
in a relational database, tables, views, synonyms, and aliases
all logically appear
to be identical to the end user: as rows and columns.
It is true that there are certain operations that can not
be performed on certain types of views, and users who need to
know this will generally be sophisticated users.
For example, very few shops allow end users to update any
table they want using QMF, SPUFI, or some other tool that uses
dynamic SQL. Updates,
deletions, and insertions (the operations which are not
available to some views) are generally coded into application
programs and executed in batch or via on-line transactions.
The end user does need to query tables dynamically.
Now you tell me, which name will your typical end user
remember more readily when he needs to access his marketing
Arguments For Indicators
shops believe they have very valid reasons for embedding an
object type indicator character into database objects; view
names, in particular. Let's
examine these arguments:
Embedding a V into our view names enables our DBAs to
quickly determine which objects are views and which are tables.
Many of these shops do not embed a T into the table name,
but feel that a V in the view name is necessary.
It is believed that the DBA will be able to more easily
discern views from tables.
But, rarely do these shops force an S into synonym names
or an A into alias names. Even
if they do, it is usually overkill. Any good DBA already knows which objects are tables and which
are views, and if s/he doesn't, a simple query against the
system catalog will clarify the matter.
example, in DB2, this query will list all table-like objects:
WHERE TYPE = "T"
SELECT NAME, CREATOR, "ALIAS"
WHERE TYPE = "A"
SELECT NAME, CREATOR, "SYNONYM"
SELECT NAME, CREATOR, "VIEW"
ORDER BY 3, 1
It is necessary to code view names differently so that
users understand that they are working with a view and not all
operations can be performed on the view.
But all operations can be performed on some views.
And not all operations can be performed on all tables
either! What if the
user does not have the security to perform the operation?
For example, what is the difference, from the user's
perspective, between accessing a non-updateable view and accessing
a table where only the SELECT privilege has been granted?
It or Lose It
common problem with database naming conventions is unnecessary
size restrictions. Using
DB2 as an example, most objects can have a name up to 18
characters long. But,
in many instances, shops establish naming standards that do not
utilize all of the characters available.
This is usually unwise.
to Figure 2 for maximum and recommended DB2 object name lengths.
Notice that except for indexes, the recommended length is
equal to the maximum length for each object.
DB2 Object Reference
* where TYPE = T
TYPE = A
are indexes singled out in DB2?
This is an example of a compelling reason to
bypass the general recommendation.
Developers can explicitly name DB2 indexes,
but they can not explicitly name DB2 index spaces. Yet, every DB2 index requires an index space name.
The index space name will be implicitly
generated by DB2 from the index name.
If the index name is 8 characters or less in
length, then the index space name will be the same
as the index name.
However, if the index name is greater than 8
characters long, DB2 will use an internal,
proprietary algorithm to generate a unique, 8 byte
index space name.
As this name can not be determined prior to
index creation, it is wise to limit the length of
index names to 8 characters.
This is a good example of the maxim that
there are exceptions to every rule.
final troublesome naming convention is embedding
specialized meaning into database object names.
The name of an object should reflect what
that object is or represents. However, it should not attempt to define the object.
examine this revised rule by means of an example. Some shops enforce DB2 index naming conventions such that the
type of index is embedded in the index name.
Consider the standard shown in Figure 3.
3. Typical Index Naming Standard
two potential problem areas with this standard:
An embedded X identifies this object as an
Embedded meaning in the form of indicators
detailing the type of index.
embedded indicator character "X", although
unnecessary, is not as evil as indicator characters
embedded in table-like objects.
Indexes are not explicitly accessed by users.
Therefore, obscure or difficult to remember
naming conventions are not a problem. The same arguments hold true for table space names.
fact, indicator characters may actually be helpful
to ensure that table spaces and indexes are never
named the same.
Table spaces and indexes, within the same
database, can never be named the same.
DB2 uses a name generation algorithm to
enforce uniqueness if it is attempted.
exception results in another revised rule:
second potential problem area poses quite a bit of
trouble. Consider the following cases which would cause the embedded
meaning in the index name to be incorrect:
The primary key is dropped.
A foreign key is dropped.
The index is altered from non-unique to
unique (or vice versa) using a database alteration
What if an index is defined for a foreign
key, but is also unique?
Should we use an "F" or a
Or do we need another character?
have also seen naming conventions which indicate
whether the index is clustering ("C") or
This is not a good idea either.
Misconceptions can occur.
For example, in DB2, if no clustering index
is explicitly defined, DB2 will use the first index
created as a clustering index.
Should this index be named with an embedded
"C" or not?
look at one final example from the real world to
better illustrate why it is a bad idea to embed
specialized meaning into names.
Consider what would happen if we named
corporations based upon what they produce.
When IBM began, they produced typewriters.
If we named corporation like we name database
objects, the company could have been named based
upon the fact that they manufactured typewriters
when they began.
IBM might have been called TIBM (the
"T" is for typewriters).
And guess what, they don't make typewriters
any longer. What would we do? Rename
TIBM or live with a name that is no longer relevant?
conventions evoke a lot of heated discussion.
Everybody has their opinion as to what is the
best method for naming database objects.
Remember, though, that it is best to keep an
open mind. Johnny
Cash may have been upset that his father gave him a
girl's name in the hit song "A Boy Named
Sue," but that was before he knew why.
If this article caused you to think about
naming conventions from a different perspective,
then I will consider it a success.