Normalization Opinion

[wideasleep]

I'm laying out a form and am in question on my normalization of data and I would appreciate a few opinions if this could be layed out better. I'm trying to follow a one to many all the way through on this. Here's the layout of my tables.

tblBuildingInfo
ID(pk)
Building_Number - lookup
Inspection_Date - date/time
Inspection_Time - date/time

tblChecklistIte ms
ID(pk)
Checklist_Item( fk) - lookup

tblComments
ID(pk)
Substandards - memo
Corrective_Acti on - memo
Reason_Not_Corr ected - memo

In the ChecklistItems table the checklist field is a lookup field of many checklist items. The checklists will differ depending on each building selected. Each checklist will be the same for at least three other buildings. Each Item in the checklist is a yes/no field with the default of yes. If an item is unselected indicating, "no" I need to record information in the Comments table. I am questioning if this layout is correct in using the lookup table. I need to be able to make comparisons by date for each checklist item.

Thank you, I appreciate any comments or advice.

[MMcCarthy]

Have a look at this tutorial on Database Normalisation.

As I understand what you are saying tblChecklistIte ms is a Join table between tblBuildingInfo and Checklist lookup table. This way you shouldn't need the yes/no field as the only items from the checklist appropriate to the building will have a corresponding building ID.

The following layout shows these revisions. Also if comments are one to one reference with the building then the following tblComments should work. However, if the tblComments table refers to the checklist items then you will need to include the Checklist_Item fk in tblComments.

It's difficult to give further advice without more information but the tutorial should help you.

tblBuildingInfo
BuildingID(pk)
Building_Number - lookup
Inspection_Date - date/time
Inspection_Time - date/time

tblChecklistIte ms
BuildingID(fk) - referencing tblbuildingInfo
Checklist_Item( fk) - lookup

tblComments
CommentsID (pk)BuildingID( fk) - referencing tblbuildingInfo
Substandards - memo
Corrective_Acti on - memo
Reason_Not_Corr ected - memo

[wideasleep]

Thank you Mary,

I read a couple of articles on normalization including the one on the site but I have been questioning my layout on this project for a while. Your layout seems to make more sense. I guess I've limited myself to the use of foreign keys when I could always use more than one.

Curt

[MMcCarthy]

Thank you Mary,

I read a couple of articles on normalization including the one on the site but I have been questioning my layout on this project for a while. Your layout seems to make more sense. I guess I've limited myself to the use of foreign keys when I could always use more than one.

Curt

The basic rule is anytime you find yourself with a many to many relationship then create a Join table with a joint primary key made up of foreign keys referencing the primary key of both tables.

[wideasleep]

One other thing that might help in understanding what I'm doing for my employer is that they want to make a comparison on each item from a day to day basis ie; if a substandard existed one day vs. another day. This was why I went with a y/n checkbox for each item. I was thinking on laying out each checklist Item in it's own table but felt that might confuse things.