By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,183 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Normalization Opinion

P: 72
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

tblChecklistItems
ID(pk)
Checklist_Item(fk) - lookup

tblComments
ID(pk)
Substandards - memo
Corrective_Action - memo
Reason_Not_Corrected - 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.
Sep 17 '07 #1
Share this Question
Share on Google+
4 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Have a look at this tutorial on Database Normalisation.

As I understand what you are saying tblChecklistItems 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

tblChecklistItems
BuildingID(fk) - referencing tblbuildingInfo
Checklist_Item(fk) - lookup

tblComments
CommentsID (pk)BuildingID(fk) - referencing tblbuildingInfo
Substandards - memo
Corrective_Action - memo
Reason_Not_Corrected - memo
Sep 18 '07 #2

P: 72
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
Sep 19 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Sep 19 '07 #4

P: 72
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.
Sep 19 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.