Hi there,
I must tie together the data in 3 main tables:
MtlbLegal
MtblChem
MtblOps
There is another main parent table which will always have data:
MtblDoc
I've made a join table:
JtblLegalChemOps, including the ID's of each of 1st 3 tables above plus MtblDoc.
Relationships:
MtlbLegal, MtblChem and MtblOps are linked to the join table in one-to-many relationships. The join table is linked to MtblDoc in a many-to-one relationship.
Here's the problem:
In many instances, users will not be able to enter data into each of the 3 tables: MtblLegal, MtblChem or MtblOps. But, when they can, the data from these tables must be tied together. Often times, users will enter data into only 2 of the 3 tables; this data must be linked together.
However, referential integrity, or a substitute code, must be made between all the tables because when a user updates or deletes a record in MtblDoc, associated records in the other tables must be removed. Does anyone know how I should approach this?
Any advice/assistance would be greatly appreciated. Thanks,
Eric
I must tie together the data in 3 main tables:
MtlbLegal
MtblChem
MtblOps
There is another main parent table which will always have data:
MtblDoc
I've made a join table:
JtblLegalChemOps, including the ID's of each of 1st 3 tables above plus MtblDoc.
Relationships:
MtlbLegal, MtblChem and MtblOps are linked to the join table in one-to-many relationships. The join table is linked to MtblDoc in a many-to-one relationship.
Here's the problem:
In many instances, users will not be able to enter data into each of the 3 tables: MtblLegal, MtblChem or MtblOps. But, when they can, the data from these tables must be tied together. Often times, users will enter data into only 2 of the 3 tables; this data must be linked together.
However, referential integrity, or a substitute code, must be made between all the tables because when a user updates or deletes a record in MtblDoc, associated records in the other tables must be removed. Does anyone know how I should approach this?
Any advice/assistance would be greatly appreciated. Thanks,
Eric