SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, June 19, 2006

1NF Violation?

SQL Apprentice Question
I'm evaluating an existing database for a medical clinic. It has many tables
with *identical columns*. Apparently the existing dba thought that doctors
with different specialties were different entities: like "Cardiologists"
"Hepatologists" "Etc". These tables store contact information, including:
Name (first and last all in one column), Address, Location (whatever the
heck that is), PhoneNumber, PhoneExtension, FaxNumber. The DDL of these
tables is *identical* (with the only difference being the table name).

Does this violate the "no repeating groups" rule of 1NF?

I've generally thought that rule was for columns like Phone1, Phone2, Phone3
(within the same table). But these are repeating groups *among* tables. Yes?
Does the "no repeating groups" rule mean both *within* and *among* tables?

Celko Answers
>> Does this violate the "no repeating groups" rule of 1NF? <<

No, this is called "attribute splitting" and it is one of the
"non-normal form" redundancies. In this example, you have converted
the values of an attribute into multiple tables. Another way to do
this screw up is to split the values into columns within the same table
(usually bit flags that should be modeled as a single code, but there
are other ways).

Tom Johnston did a series of articles years ago on other redundancies
that are not taught in begining classses.

No comments: