Solved

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

Posted on 2011-03-04
6
273 Views
Last Modified: 2012-05-11
Hello, I am new to database design and some of the experts here have said that my forms should follow standard normalization procedures to avoid future problems.  I have read through a few online sources but I'm still not 100% sure how I should go about doing this in the form that I want to attach.  
First normal form standard says that I should not have repeating groups, but I have many.  This is because I am trying to capture all of the information in academic articles (Such as http://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0014550).  If I were trying to capture the patient and demographic data in table 1 from this article I would need to have four groups for race.  So I thought it would be good to have in my table Race1, Race2, Race2, Race4 etc... and then Race1Per for the percent of the population etc.  However, this seems to go against normalization practices.  By 1NF standard I should have a table something like: [Call Number] [Race] [RacePer] and then just repeat the entry each time for the race options.  How could I accomplish this with a form?  I'm at a loss right now for how best to construct these forms/tables and any help would be greatly appreciated.

Thank you,
Bevo
EE-ABS-Example.accdb
0
Comment
Question by:Bevos
6 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 35038125
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.
0
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 100 total points
ID: 35039174
see also this demo about creating a third table to break a many to many relationship between two tables

http://office.microsoft.com/en-us/access-help/demo-set-the-relationship-between-two-tables-HA010254901.aspx?pid=CH100739911033

there are also lots of information on that link regarding table normalization
0
 

Author Comment

by:Bevos
ID: 35039534
Hi Capricorn1 and Jim, thanks for the great resources. I think I've normalized each table now, but I am running into many problems with the forms now (maybe because of the database relationships?).  Previously, I had everything on this form in one table with the exception of some fields which were looked up to populate combo/list boxes.  Now, I am getting an error when I open the form (it is asking me for fields in these separate, related tables) and showing me a header only.  I'm kind of stumped on how to add all these new, separate table sources into the form.

Here is now I envision the form working in its new, normalized fashion (please tell me if this is possible or if I am barking up the wrong tree here).

1.) Users will enter general study characteristics in table tblPtCharacteristics which has the primary key, [Call Number]
2.) Users will define groups for this record which are stored in tblGroup (there can be one or many 'groups' per record)
3.) Users will describe the characteristics of the group defined in item 2.  (tblRaceDesc, tblStageCatDesc, tblMale, tblHistologySiteDesc, tblDiseaseDesc) which are in their own tables now and related to the [Call Number] in 1 and also have a field for the group that they are describing
(I am not sure how I do this, the linking to both call number and group defined in 2.  I tried to put [Group] in each of the tables mentioned above, but I am not sure if I am doing this correctly.)
4.) I want users to be able to click a button on the record to add a new group, or any of the other table categories
5.) Create some kind of summary for the [Call Number] record by [Group] so that the user can see quickly if he or she has entered everything necessary.

Any help would be excellent. I think I understand some of this stuff, but it is hard for me to understand everything from the examples shown above.

Thanks again,
Bevo

(Also, why is Access creating strange items in my 'fields list' such as Disease_tblDiseaseDesc)
EE-ABS-Example.accdb
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 200 total points
ID: 35059099
First....
Forget even thinking about forms until you are certain that the tables are properly normalized and related.

You have 19 tables, yet only 7 of them are displayed as being related in the relationship window.

You also have a great many of your tables do not have primary keys, thus preventing you from establishing "Referential Integrity"..
So perhaps we should go back even further...

Jim has posted a great deal of useful information in his post above.

But remember no Expert here can really provide any *Specific* advice on designing a database that they are not intimately familiar with.

Your follow up post now lists 5 *concerns*.
So it is difficult to see what the "One" question is here, and what the ultimate "Solution" here needs to be.

So like Jim, I applaud you for wanting to do things correctly as a first step.

So I think your first basic question should be:
  "This is what I want to keep track of, what tables/fields do I need."
Then when you get this question resolved, post another question, something like this perhaps:
  "Here is my design, how do I relate the ___ Table to the ___ Table to make sure that ____."
Then once this is resolved move on to the next set of tables.
Each question should be followed up by you posting your results and clarifying each solution.

In this way each question is resolved and serves as a stepping stone (Building block) for the question.
Makes since
;-)


From what you posted, it is difficult to determine if all your tables are designed properly because we don't know the specifics of your requirements or design parameters.

In other word, simply joining tables that are 3NF does not automatically make your design valid.

Jim and or/cap may be able to go into the specifics of this a bit deeper...

It just seem to me that the question is too broad to be covered in one specific "Question" here.
And as such, should perhaps be better answered as multiple, sequential questions.

JeffCoachman
0
 

Author Comment

by:Bevos
ID: 35059341
Thanks for the great advice everyone.  I will be closing this question and posting smaller, more specific questions like Jeff has suggested.  I appreciate everyone's advice and their patience with me as I try to learn this topic.  

Thanks again,
Bevo
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35059471
OK

Great.

I'll see if I can assist...
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now