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:

create
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:

create
table customer
( id datatype
, name datatype
, birthdate datatype
);

select customer.id
, customer.name
, 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:

plandatamodel2.jpg

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 plantype.id = p.plantype_id
and plansubtype.id = p.plansubtype_id
and plan.id = pf.plan_id
and frequency.id = pf.frequency_id
and coverage.id = fc.coverage_id
and planfrequency.id = fc.planfreqency_id
and premiumtype.id = cp.premiumtype_id
and frequencycoverage.id = 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.

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