Access 2007: "I want to be normal." or, help me understand and make a table/form normal

Bevos,

  First, let me congratulate you on wanting to take this important first step with your DB, which is starting out with a proper design.  Many do not bother to do so and as a result, end up with a lot of problems and a poorly performing application that is difficult to maintain and use.

  First thing is that you want to have a table for each "thing" in your database; ie.  Patents, Races, Languages, etc.

  For each of those "things", you want all the data associated with it to be in that table so that data will be stored only once.

  So for patients, I might have a table like this:

PatientID - Autonumber - Primary Key
LastName - Text
FirstName - Text
DateOfBirth - Date/Time

  Each of those fields should be indivisible or in other words, should contain only one value.  For example, you do not want this:

"English, French, and German"

  as a single field for "languages" in a patient record.  That's first normal form.

  Second normal form is that every field in a table depends on the primary key.  In other words, it belongs to that "thing".

  Here's an example.  Lets say I wanted to record events that these people might come to.  You might do a table like this:

tblEvents
EventName
AttendeesLastName  
AttendeesFirstName

and the data might look like this:

Cancer Answers   Dettman   Jim
Cancer Answers   Smith  John
Cancer Answers   Doe   Jon
Cancer Answers   Doe   Jane
Cancer 101          Dettman Jim
Cancer 101          Doe  Jon

   Note that the event name repeats for each particpient.  Also note that the participients names repeat every time they attended an event.  That's not what we want as those fields belong to other "things".  The fields last and first name belong to a "patient", not to an "event".

  What we really want is to record the fact that they attended an event.   By doing the above (storing the name in multiple places) if we needed to update their name, we would be forced to go through every record in that table, plus update thier patient record.

  So instead we would do this:

tblEvent
EventID - Autonumber - Primary Key
EventName - Text
EventDate - Date/Time
MaxNoOfAttendiesAllowed - Long

tblEventAttendies
EventID - Long - Lookup to tblEvents
PatienID - Long - Lookup to tblPatients

  tblEvent is the list of events:

EventID   EventName
1             Cancer Answers
2             Cancer 101

and tblEventAttendies is a list of who came.  However it doesn't store a name, but a pointer to the "patient" (which is called a foreign key), which has all the information on the patient.

  When we create a form or report then, we use a query or view, to join the tables based on those pointers and have access to the data even though it is being stored only once.

  The other part to this is repeating groups.  Above, I said that I spoke three languages.  Because of 1NF, I would need to store each seperately:

   "English"  "French" "German"

  The problem I have now though is I have a repeating field:

Jim Dettman       "English"  "French" "German"
Jon Doe      "English"  "French" "German" "Spanish"
Jane Doe     "English"

    This is a red flag that I need to do something different.  When you have repeating fields, you need to break them out into seperate tables like this:

tblPatients
PatientID - Autonumber - Primary Key
LastName - Text
FirstName - Text
DateOfBirth - Date/Time

tblLanguages
LanguageID - Autonumber - Primary Key
LanguageName - Text

tblPatientLanguages - One record per patient/language combination
PatientID - Long - Lookup to tblPatients
LanguageID - Long - Lookup to tblPatients


  with this setup, which is a "one to many" relationship (one patient can have one or more languages), I can have a patient and record as many languages as I need to (or just one) simply by adding records to this table.

  3NF (Third nomal form) says that you don't store calculated values.  For example, you would not store a patients DOB and their age.   Age is a calculated value based on the date and their DOB.

  Another example, an extended price, which is:  Quantity * Price .  You store quantity and price, but not the result of quantity * price

  I skimmed over a couple of things but those are the basics, so I am going to stop there and let you ask any questions you might have on the above.

JimD.