How can 3 tables be linked in many-to-many relationships?

[Accesser]

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

 

[JeremyNYC]

Eric,

This is a really interesting problem. My guess is that it's interesting enough that there's some literature on the subject out there somewhere, though I've never seen it myself.

After thinking about it for just a couple of minutes, I can't come up with any solutions, just a couple of possible workarounds...

One thought is to not use cascading deletes and do the follow-on deltions yourself, with code.

Another, probably better, solution is to never let users delete anything, instead giving them the ability to archive records. This is the strategy I'm moving towards in all my databases. Just add a flag field and always hide records with that flag set to true, unless you're in there as an administrator, and you're looking for the deleted records.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Accesser]

Thanks Jeremy,

Not enforcing ref integrity allows the data from the 3 tables to be entered, combined and tracked in the join table.

I'm rather new to VBA and am not sure about how to go about adding the flag field(s) you mentioned, or coding for the follow-on deletions/updates for that matter. Could you maybe elaborate a little on your choice?

Something else I should mention is that there's kind of a tough relationship among the 3 tables:

1 record in MtlbLegal may be associated with > 1 records in MtblChem;
1 record in MtblOps may be associated with > 1 records in MtblChem;
1 record in MtblOps may be associated with > 1 records in MtblLegal; etc.

Thanks again,

Eric

 

[JeremyNYC]

Eric,

I didn't mean to imply getting rid of RI, just the cascading deletes.

The flag fields would just be a single Yes/No field in each table, named something like "Deleted" or "Archived". Each time the user tries to delete something, just set that field to true for the record, instead of actually deleting it. It is a good bit of work, and will require that you get into writing VBA code a good bit. I've not implemented it as fully as I would like, so I'm a little hesitant to advise you on how to proceed.

Hmm. I'm just looking at your tables again. Do you need the lookup table? That might be a wacky question, and I'm guessing the answer is yes. But just in case, what do you do with the lookup table? I wonder if it could be replaced with a query. These are all one to many relationships, not many to many, so maybe you don't need the lookup table.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Accesser]

Jeremy,
Very good comments and questions. Thanks for your replies.

In terms of RI, if it's enforced, then I receive errors like 'a related record is required' in one of the other tables. I'm trying figure out a work-around for that (data won't be entered into all of the 3 tables all of the time).

In terms of lookups, yes, I've been trying to figure that out as well.

For data entry, I was thinking about having a main form based off of MtblDoc, and a subform based off of JtblLegalChemOps. Several buttons on both forms, when clicked, would open up forms based off the 3 other tables. When one or more of these forms are closed after entering data, the subform for JtblLegalChemOps would be requeried. I don't think this is going to work though. Guess I'm just confused.

 

[JeremyNYC]

Eric,

Because I don't know your data I was having a hard time looking at this, so I tried to come up with a parallel. Turned out to be easier than I thought, though I'm not sure I got it right!

Ops = Leagues
Legal = Teams
Chem = Players

1 record in MtlbLegal may be associated with > 1 records in MtblChem;
1 record in MtblOps may be associated with > 1 records in MtblChem;
1 record in MtblOps may be associated with > 1 records in MtblLegal;

1 record in tblTeam may be associated with > 1 records in tblPlayer;
1 record in tblLeague may be associated with > 1 records in tblPlayer;
1 record in tblLeague may be associated with > 1 records in tblTeam;

What I would do if these were the correct tables is get rid of the relationship between tblLeague and tblPlayer, which would translate to getting rid of the relationship between tblOps and tblChem. But I don't know if that works in your situation.

Does it?

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[Accesser]

Here's an example of the data:

MtlbLegal=property legal descriptions, like parcels, sections, townships, ranges, etc.

MtblChem=chemical data, mostly chemical compounds and their properties, like specific gravity, hazard rank, etc.

MtblOps=operations, such as manufacturing, assembly, storage, office space, maintenance, and their dates and other assoc. comments.

So, at 1 property (legal), there may be a record of many chemicals used or operations conducted. Likewise, 1 operation may be conducted at various properties or locations (legals), and/or involve numerous chemicals.

I've considered making the MtblOps a mandatory table, in which users must enter a type of operation, and if they don't know what the operation may be, they could enter 'unknown.' However, there's still the problem of joining all 3 tables.