Relationship Question

[hi there]

Howdy all,

i'm putting together an inspection database and i've got a question about proper normalization between the tables. here's the business rules:

1. have a tblArea = corresponds to geographic area of a facility e.g. kitchen, living room,..etc
2. have a tblMajorItems = corresponds to the major items located within an area e.g. refrigerator. Each major item will be associated with an area.
3. have a tblComponent = corresponds to the component to be inspected. each component will be associated with an "area" and each component uses a major item as a landmark to find the component, however it is not true to say that because the component is associated with a "major item" it is associated with the "area" the major item is associated with. therefore each component must be explicitly associated with an area and not pass through a one-to-many daisy chain from tblArea to tblComponent.

so i'd like to use tblMajorItems as a lookup table for tblComponent, but the business rules do not allow tblComponents to inherit their relationship with tblArea through tblMajorItms.

what is the proper normalization schema for this scenario?

many thanks for any help with this. have a good day!

 

[mresann]

Establish the same type of relationship between tblArea and tblComponent, as you do with tblArea and tblMajorItems. So far, so good. Now you need to associate tblComponent with tbleMajorItems, WITHOUT reference to tblArea. That means you need to establish a many to many relationship btween tblComponent and tblMajorItems. To do this, create a Join table, that contains the Primary Keys from both the component and major items table, and call it, say, tblCompMajorItemsJoin.

Here is the final skeleton structure:

tblArea
AreaID (PK)
MajorItemID (FK)
ComponentID (FK)
...+ other Area fields

tblMajorItems
MajorItemsID (PK)
...+ other Major Item fields

tblComponent
ComponentID (PK)
...+ other Component fields

tblCompMajorItemsJoin
MajorItemsID (FK, Co-PK)
ComponentID (FK, Co-PK)
...+ other fields relating to the Major Item / Component join

 

[hi there]

hi mresann,

thanks for the reply. i want to make sure i understand you correctly. the current schema is

tblArea > tblComponents is one-to-many with tblArea as the parent

tblArea > tblMajorItems is one-to-many with tblArea as parent

so in order to explicitly define the relationship between tblMajorItems to tblComponents to handle the business rules:

1. one MajorItem can have multiple components associated with it
2. one component is not associated with many MajorItems
3. each component must have an explicit relationship defined between an area. it cannot inherit it's relationship through tblMajorItems.

i need to create a many-to-many to establish the relationship between the tblMajorItems and tblComponents?

i just wanted to make sure i understood you correctly. i guess what's throwing me off is business rule #2. so my final schema would be

tblArea
AreaID (PK)
....


tblMajorItems
AreaID (FK)
MajorItemID (PK)
...

tblComponents
AreaID (FK)
ComponentID (PK)
....

tblMajorComponentJunction
ComponentID (PK)
MajorItem (PK)
...


many thanks again for all of you help.