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?
>> 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.