Table Design: Categories and Fields

[srpatel]

I have 2 tables that I have added to an existing database. Table 1 contains all the description values in one the columns. Table 2 contains 5 entries which are the group or categories that the descriptions belong to. For example:

tblDescriptions

Column Headers: -
DescID, Description

Fencing
Bridges
Lighting
Ventilators

tblGroup

Columns: -
GroupTypeID, GroupType

Grp A
Grp B
Grp C
Grp D
Grp E

The issue I have is, some of the description values appear in more than 1 group. E.g. fencing could appear in Grp A, Grp B and Grp C and not in Grp D and E. I can’t seem to get around a suitable design on how to manage the data. Do I need to create another table or add an existing column within one of the tables with some sort of relevant ID code?

Any help would be great.

Thanks
Shreekant

 

[PHV]

Have a look here:

http://r937.com/relational.html

Seems you have a many to many relationship.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[johnherman]

Agree. You need a bridge or relation table, say,

tblDescriptionsGroup
DG-ID - primary key to the bridge table
DescID - foreign key to Description table
GrpID - foreign key to Group table.


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[srpatel]

Thank You PHV and Johnherman. I have worked through it and it does seem to work now.

Srp