Table help -- getting started

[Renzi]

hello,

I am having some difficulty starting my database. I have worked with Access 2002 and have massaged data into useful information for other databases. I am having trouble determining useful tables and eliminating redundancy. I have attached a.bmp file.

So far I have 20 different "Features". Each one of these features can belong to one or more "Groups". Each "Group" for a particular "Feature" will have a selection list of Multiple "Causes" a user can select from. Once the user selects a "Cause" -- then each "Cause" has a list of "Corrective Actions" the user can select particular to that "Cause".

If anyone would be so kind as to possibly point me in the right direction as to what I should do as far as structuring my tables properly, I would be appreciative. Thank You in advance.

 

[addy]

I think I would look at something like:

tblFeature

FeatureID 'Unique ID
FeatureDesc 'Description of Feature

tblGroup

FeatureID
GroupID
GroupDesc

tblCause
GroupID
CauseID
CauseDesc

tblCorrectiveAction
CauseID
CorrectiveActionID
CorrectiveActionDesc

Pretty much how you have set it out in your .bmp - A one to many relationship from Feature to Group, related by the Feature ID etc. etc.

 

[Renzi]

Thanks for taking the time to look at my layout and respond. I will try and implement something -- If I have problems I may be back for opinions. It is appreciated.

 

[Renzi]

How exactly would that table structure work? I looked at it this morning and in tblGroup I see that I can't name a PK. I have 20 features in tblfeature. Each feature could possibly be in each group. Maybe I am not understanding this properly. Any insight?

 

[addy]

Can each group have multiple features in it? I may have misunderstood your original post!

 

[addy]

Also, can each Cause be related to more than one group?

 

[Renzi]

Yes -- each group can have more than 1 feature -- or Each of the 20 features can could potentially come from more than 1 group. Also each cause canNOT be related to more than 1 group -- for a single feature in a single group there can only be one cause. ie.) If the feature is ID the group could be MACHINE, TOOLING, or GAGER.
IF the feature is ID and the group is GAGER then the cause could be A DIRTY LIGHTPAD
IF the feature is THREAD and the group is GAGER then the cause could be A DIRTY LIGHTPAD as well.

The cause A DIRTY LIGHTPAD is ONLY related to the GAGER group and no other group.

Sorry for the extended response and thank you for your time.

 

[Renzi]

Yes -- each group can have more than 1 feature -- or Each of the 20 features can could potentially come from more than 1 group. Also each cause canNOT be related to more than 1 group -- for a single feature in a single group there can only be one cause. ie.) If the feature is ID the group could be MACHINE, TOOLING, or GAGER.
IF the feature is ID and the group is GAGER then the cause could be A DIRTY LIGHTPAD
IF the feature is THREAD and the group is GAGER then the cause could be A DIRTY LIGHTPAD as well.

The cause A DIRTY LIGHTPAD is ONLY related to the GAGER group and no other group.

Sorry for the extended response and thank you for your time.