Enforcing referential integrity on multiple fields 1

[zandsc1]

To start, let me say that I'm trying to model this database after an existing documentation system. I wish I could change the system structure, but I'm stuck with trying to model what someone else has made.

There are two types of documents that I want to set up relationships between:

Finished Goods (FG)
Multi-Document Work Instructions (MDWI)

Some of the FG are unique and contain their own work instructions. Other FG are similar to each other, and use a MDWI for their work instructions (i.e. if two FG have identical work instructions, but are slightly different sizes, then the MDWI houses the instructions to make those FG). There are cases where a FG uses multiple MDWI, but never where it uses a FG and a MDWI for it's instructions. I have structured the tables as such:

tblFG: contains an entry for each FG
tblMDWI: contains an entry for each MDWI

Now I want to create a table that describes where the work instructions for each FG are. The table might look something like this:

FG | WI
FG1 | FG1
FG2 | FG2
FG3 | MDWI1
FG4 | MDWI1
FG5 | FG5
FG6 | MDWI2
FG6 | MDWI3
FG7 | MDWI2
FG7 | MDWI3

etc.

Now I'd like to be able to set up the relationships such that the only values that can be in the 'FG' field of this table are values in 'tblFG', but that the values in the 'WI' field can be in 'tblFG' or in 'tblMDWI'. I can set up a 'One to Many' between the 'FG' field in this table and 'tblFG', but then I cannot setup a 'One to Many' on the 'WI' field.

Should I be structuring my tables differently? Am I overlooking some way to set up this relationship? Is this just not possible because of the way the system is designed?

See attached picture. This is as far as I got before running stuck.

 

[BNPMike]

What you have is a many-to-many relationship:

FG-> FGWI <- WI

You don't need to put any FG entries in WI. Create two joins leaving the WI one an outer join.

Your data is
FG
FG1
FG2
etc

WI
MDWI1
MDW2
etc

FGWI

FG3, MDWI1
FG4, MDWI1
FG5, FG5
FG6, MDWI2
FG6, MDWI3
FG7, MDWI2
FG7, MDWI3

 

[zandsc1]

In other words, only include the entries where the WI is not equal to the FG?

I'd considered this option, but it's less than ideal for some of the other relationships I'm constructing. If that's the only way to do it, I'll find a way to make it work.

 

[MajP]

Your simple table is actually demonstrating some really complex database design. I disagree with the recommendation, and your initial design is correct. Just so you know, you have a many-to-many, self-referencing table with a foreign key to two different tables. I have never seen it, but I think it is correct both theoretically and practically.

First. Index both fields so that you can only have a unique case of each combination. You can enforce referential integrity on the first field to the FG table. You cannot enforce referential integrity on the second field. However, I would assume you are selecting values from a pulldown. You can build a union query that only returns itself and MDWI values that are not already assigned.

The only thing you might have to program is a cascading delete on MDWI. If an MDWI is deleted you want to delete all records that relate to that MDWI.

 

[zandsc1]

Thanks for the tip MajP, I'll give that a try.

 

[BNPMike]

You can add the entries where WI is equal to FG if you want to. It seems a bit pointless but it's free country.

MajP is seeing something I don't. This is a common type of situation. He/She is also talking about integrity constraints which I know you referred to originally but I don't think you meant that - I think you were thinking simply about joining things rather than particularly about the database stopping you from doing certain things. Telling the database about relationships is not essential. SQL does that for you.