Multiple Relationship Join

[CommonSenseBoy]

I have a design dilemma-- I have a join table (protective gear for a specific
chemical) with one foreign key from the chemical table, and one foreign key
for the gear. The gear comes from two tables (supplies and equipment),
though, and relating both of these tables to one field causes errors since
necessarily one part number is only in one of the those tables. I can either
stop enforcing referential integrity (not sure what that could do down the
line), create a second field for the second table and stash a null value in
whichever field isn't being used for a particular record (inelegant and AFAIK
in violation of 4nf), or split into two join tables and cull all the data in
a query. What would be the soundest way to relate these together?

Thanks,
Bill

 

[mnature]

I may be oversimplifying this, but here is my logic. You have protective
gear that can be used while working with chemicals. You use a join table,
because you have a many-to-many relationship between the protective gear and
the chemicals. However, protective gear is composed of supplies and
equipment, which are listed in their own tables. It would seem that you need
two more join tables between the gear table and the supplies and equipment
tables:

tbl_Gear
GearID (PK)

tbl_Supplies
SupplyID (PK)

tbl_Equipment
EquipmentID (PK)

tbl_GearSupplies
GearID (PK)
SupplyID (PK)

tbl_GearEquipment
GearID (PK)
EquipmentID (PK)

 

[Tim Ferguson]

=?Utf-8?B?Q29tbW9uU2Vuc2VCb3k=?=

I have a design dilemma-- I have a join table (protective gear for a
specific chemical) with one foreign key from the chemical table, and
one foreign key for the gear. The gear comes from two tables
(supplies and equipment), though, and relating both of these tables to
one field causes errors since necessarily one part number is only in
one of the those tables.

You need a solution called subtyping: this involves an extra table.

Start with a table called something like ProtectiveSolutions -- this will
contain all the attributes that are common to Supplies and Equipment
(say, EnglishName, WhereInstructionsAreKept, TrainingRequired, etc) and
an identifier (say, SolutionID) as the Primary Key.

Now, each of the tables Equipment and Supplies has a primary key called
SolutionID which is _also_ a foreign key pointing at
ProtectiveSolutions.SolutionID (and not the other way round). This means
that every Supply and every Equipment has to be a member of
ProtectiveSolutions. If neccessary, you can make a constraint to prevent
a single solution being both a supply and an equipment. This ensusre that
no supply and equipment can have the same SolutionID.

Now, the table ProvidesProtection has foreign keys that point to
Chemicals.InternationalNumber and ProtectiveSolutions.SolutionID.

Try this:

ProtectiveSolutions --< ProvidesProtection >-- Chemicals
| |
Supplies -+ |
Equipment --+

Hope that helps
(PS Hmmmm maybe the word Solutions was not such a good one in context,
but I think you should be able to see what I was getting at.)

Tim F

 

[CommonSenseBoy]

Thank you very much, I was completely in the dark about subtyping. I'll try
it @ work tomorrow and report (hopefully good) results.

Bill