Fixing A Stupid Naming Convention

I’m going to talk about one of the most prevalent and stupid naming conventions in data modeling. This is the convention of incorporating the table name into the column name. Here’s an example:

table customer
( customerid datatype
, customername datatype
, customerbirthdate datatype

Why is this convention used? Because of a stupid SQL coding convention called table name aliasing. Here’s an example of its use:

select a.customerid
, a.customername
, a.customerbirthdate
from customer a;

The genius who is incorporating the table names into the column name is doing so because he is using table name aliasing. It would be just as effective and more intelligent to do the following:

table customer
( id datatype
, name datatype
, birthdate datatype

, customer.birthdate
from customer;

No table name aliasing is used and the same information is conveyed. Not only that, but your data models are less verbose. In otherwords, don’t use table name aliasing at all. It is a useless feature that conceals the uniqueness of your columns and leads otherwise intelligent data modelers to introduce redundant elements into column names. The only time you should incorporate a table name into a column is when it represents a foreign key.

Here’s a data model using the convention I recommend:


Notice the naming of foreign keys. And here’s a SQL query using the model and the a more acceptable table aliasing convention:

select plantype.code
, plansubtype.code
, frequency.code
, coverage.code
, premiumtype.code
, coveragepremium.amount
from plantype pt
, plansubtype pst
, plan p
, frequency f
, planfrequency pf
, coverage c
, frequencycoverage fc
, premiumtype pt
, coveragepremium cp
where = p.plantype_id
and = p.plansubtype_id
and = pf.plan_id
and = pf.frequency_id
and = fc.coverage_id
and = fc.planfreqency_id
and = cp.premiumtype_id
and = cp.frequencycoverage_id;

As you can see, not using table aliases does not lead to any loss of information and additionally it is plainly apparent when you are using primary keys and foreign keys. It is time to abandon the stupid naming convention of incorporating table names into non foreign key columns.

Posted in Uncategorized. Tags: , , . 5 Comments »