Search code examples
databasedatabase-normalization

Normalizing a table to 3NF

I want to make sure I understand the concept of table normalization. I have the following example table below:

Visit#  VisitDate   Patient#   PatientBirth ProviderID  ProviderSpecialty

100000  2/15/2012   55555555    4/8/1990    862715      Nurse Practitioner
100022  2/17/2012   33333333    3/30/1998   382216      Physical Therapist
104278  2/21/2012   22222222    7/18/1972   232174      Family Medicine
103745  2/20/2012   11111111    12/22/2011  145690      Pediatrics
116238  2/23/2012   11111111    12/22/2011  145690      Pediatrics
112145  2/26/2012   66666666    8/6/1989    582361      Midwife

To break this table down into 3nf, my proposal is to break this down into 3 tables with the following groupings of attributes: (Visit#, VisitDate), (Patient#, PatientBirth), (ProviderID, ProviderSpecialty) What I'm unsure of is what we do after this to link all the tables together. My initial thoughts are to tack on the patient# as the primary key for each table, but I think that breaks the rules of 2nf. Any guidance would be greatly appreciated!

Solution

  • The first step after breaking the tables down as you have, is to determine what the relationships are between the tables. In your example, each visit is for one patient and one provider. Each patient has one or more visits and (while not show in the example) possibly one or more providers. Each provider provides for many visits and many patients.

    Using these relations you would put the primary keys from both patient and provider into the visits table. If you specifically needed a link between patient and provider, you would need to create another table with the primary keys from both, however that is not necessary for this example.

    edit: 3nf does not usually involve the specifying of foreign keys as they are usually specified after the tables have been normalised. So the tables you came up with are in 3nf already.