Many to Many to Many

[Bill R via AccessMonster.com]

I have 3 tables which are in a many-many-many relationship:

tblMSDSs - a table of Material Safety Data Sheets which describe the
chemicals in a compound
tblSuppliers - a table of manufactureres of a product containing compounds
made which require MSDSs
tblSpecs - a table of Military Specifications

and 1 table to which I must join several MSDSs:

tblParts - a table of parts in which the Spec/Supplier/MSDSs are used.

The specs are satisfied differently by different suppliers.
Consequently, the different formulae require different MSDSs reflecting their
different chemical makeup
The fly in the ointment is that, unexpectedly, the MSDSs may have many
suppliers for the same spec.

So any combination of any 2 PKs will join with many instances of the
remaining key.

My client wants to have a form in which he can choose a spec from a combo box,
have that result filter the supplier combo box, and have the selection from
both supplier and spec combo boxes return a list of MSDSs, and somehow have
all of this joined to a part.

It's confusing enough with all the data in front of me. I don't know that
anyone will be able to help me with this, but I hope one of you might take a
crack at it.

Thanks

 

[Tim Ferguson]

So any combination of any 2 PKs will join with many instances of the
remaining key.

Not a problem: it's formally described as 5th normal form (I think...)
but it's really just a case of keeping a clear head and analysing the
entities you have. I cannot fault your logic so far.

Presumably you have table for the available combinations like (= is PK, -
is ordinary field):-

AvailableThings
Material Specification Supplier Cost
======== ============= ======== ----
Rubber High Acme 230
Rubber Medium High Acme 390
Rubber High Greenhams 120
Rubber Useless Greenhams 4
Powder High Acme 28
etc
etc
etc

My client wants to have a form in which he can choose a spec from a
combo box, have that result filter the supplier combo box, and have
the selection from both supplier and spec combo boxes return a list of
MSDSs,

This is not a problem, as far as I can see. The first list is based on
something like

SELECT DISTINCT Specification
FROM AvailableThings

The second one would be something like

SELECT DISTINCT Supplier
FROM AvailableThings
WHERE Specification = "Medium High"

and the third one would be

SELECT ALL Material
FROM AvailableThings
WHERE Specification = "Medium High"
AND Supplier = "Acme Explosives"

.... of course, you'd probably want to join the other tables, so as to get
decent legible names instead of PK numbers, you get the drift.

and somehow have all of this joined to a part.

Now!! This is new:- where the Parts come in? As long as you know how to
join Parts to AvailableThings then the rest should be easy...


Hope that helps


Tim F

 

[Bill R via AccessMonster.com]

Thanks for the advice. It is a difficult nut to crack. With the addition of
the Parts table, which is the whole purpose of the app, I feel that I am
trying to represent a 4 dimensional object in a 2 dimensional space.

A part (i.e., the wing of an airplane) will utilize any number of specs (i.e.,
"Paint, Heat-Resistant, Gray" represented as "Mil-P-HR275-G" or something,
plus many more such as undercoatings, treatments, etc.), whose suppliers
(chosen at the discretion of the subcontractor supplying the part) will
necessarily use different formulations to satisfy the spec, resulting in the
generation of different lists of MSDSs (essentially, an MSDS consists of a
list of chemicals and characteristics thereof, used in the supplier's version
of the spec). So the various suppliers are joined to their specs in a many to
many join. I generate an autonumber key (INSTID) in the resulting table to
represent an "instance" of this Spec/Supplier join. I then join the INSTIDs
in a many to many join with MSDSIDs representing the MSDSs that constitute
the supplier's instance of the spec (I almost hate to mention that the
MSDSIDs are also joined many to many with "CASID"s, representing the various
chemicals which make up an MSDS. Isn't this fun? But the form in question is
only going to the level of detail of the MSDS itself).

The way the app is supposed to work, is that the subcontractor generates a
list of the parts he's supplying us. He opens the form for a part, which has
part name and description txt boxes, and 2 combo boxes, "Spec" and "Supplier".
I will have the after update events of each determine if the user has made a
selection in the "other" combo box. Once valid selections have been made in
both combo boxes, a subform should display the MSDSs associated with that
"instance" of spec and supplier.

The problem is, I don't see any way to get the subform to record the
selection automatically, the way most subforms work with regard to generating
child records for a parent form. I am considering a "Commit" button to commit
the INSTID to a Part/Instance join table. Then I could add several different
instances to each part and have a continuous subform somehow display the
MSDSs on a parent subform for each instance, in turn, joined to the part. Am
I making your hair hurt?

 

[Bill R via AccessMonster.com]

What I am trying to comprehend is whether I should have all 3 keys in one
join table (many to many to many) as that would more accurately reflect the
reality (a combination of any 2 keys could have many instances of the
remaining key) or whether I should use the more restrictive design which I am
currently pursuing (joining spec and supplier first and using the "INSTID"
generated and join it in another join table with MSDSIDs).

Does the sheer exponential weight of a many to many to many join force one to
adopt the more restrictive, purpose-driven design on which I have settled?
Admittedly, it won't easily return the specs resulting from an MSDS/Supplier
query, but I don't see any immediate use for such a query.

 

[Tim Ferguson]

I
feel that I am trying to represent a 4 dimensional object in a 2
dimensional space.

No: you are just trying to represent entities and relationships. Only one
dimension.

The problem is, I don't see any way to get the subform to record the
selection automatically,

Forget the subform. Don't even think about anything in the UI until you
have the tables design worked out -- and see the following post for more
about that.

See you downthread...


Tim F

 

[Tim Ferguson]

What I am trying to comprehend is whether I should have all 3 keys in
one join table (many to many to many) as that would more accurately
reflect the reality (a combination of any 2 keys could have many
instances of the remaining key)

I have to confess that I did not completely understand much of the
previous post: but this bit I do understand and agree with. If it's the
correct situation in reality, then it's the correct db design approach.

Does the sheer exponential weight

I don't understand this expression. Database design only recognises
things like Entities and Relationships (and, later on, Fields and
Constraints) but none of these have any kind of weight.

of a many to many to many join force
one to adopt the more restrictive, purpose-driven design

If it was purpose-driven you wouldn't be here asking questions, would
you? Seriously, you have already got to the right answer and I don't see
why you are trying to make it more complicated by breaking the design.
Did you understand the example table I suggested and the method of
extracting the various listboxes from it? You can look up information on
Fifth Normal Form if you need more examples of building this kind of
relationship.

As far as I can tell, there is a further many-to-many relationship
between AvailableThings and Parts. Since this is going to involve a table
with a four-element PK, you may want to start introducing artificial keys
although for my own purposes I wouldn't have any problem with a table
like

UsedIn(*PartNo, *Supplier, *SpecID, *Material, etc, etc)

with the FK(Supplier, SpecID, Material) referencing AvailableThings. This
is a standard 3NF situation.

Hope that helps


Tim F

 

[Bill R via AccessMonster.com]

By "exponential weight" I mean that the addition of a dimension multiplies
exponentially the number of existing elements. For example, 4X4=16, 4X4X4=64,
etc. My limited understanding of normalization suggests that this
proliferation of dimensions quickly becomes unmanageable. That's why I was
pursuing a solution that combined 2 keys (spec/supplier) in a useful table,
generating an instance of that join, and using the "INSTID" from that table
to join with MSDSs in another 2 dimensional table.

I have had situations in which I would store more than 2 fk's in a table, but
generally only 1 or 2 keys were definitive while any additional keys added
incidental criteria and usually could be null. I'm a little leary of creating
what is essentially a PK index made up of 4 fk's. It begins to mimic the
dreaded "intelligent" key.

In this particular application, the fact that 1 MSDS/supplier configuration
may be joined to several specs is interesting, but incidental to the
application. It must be permitted, but it's not a critical feature in the
sense that data will ever be added or edited from that perspective. Data will
always be added/edited from the perspective of a spec/supplier instance.

Forgive all the confusion. I have had to redisign and rethink this data
several times due to the all-too-common inability of the client to succinctly
explain the data and how he works with it. It's not their fault, they have
never been forced to consider what they do routinely in such fine detail as
this. But redisigning the database 4 or 5 times has really challenged my
imagination.

Your comments have been very helpful to clarify things and focus my thinking.

 

[Tim Ferguson]

By "exponential weight" I mean that the addition of a dimension
multiplies exponentially the number of existing elements. For example,
4X4=16, 4X4X4=64, etc.

Who cares? Tables with lots of rows is what databases do best. You are
far far better off with one correct table with twenty thousand rows than
half a dozen wrong tables with impossible relationships and five rows in
each.

My limited understanding of normalization
suggests that this proliferation of dimensions quickly becomes
unmanageable.

Normalisation has nothing to say on the matter of dimensions.

I'm a little leary of creating what is essentially a PK index made up
of 4 fk's. It begins to mimic the dreaded "intelligent" key.

No: an intelligent key refers to something else completely (it's about a
single field that contains more than one data item) -- completely
irrelevant.

There is no problem with a four-field PK -- Access allows up to ten
fields in one index. If you like, you can impose an artificial key in the
AvailableThings table, but you still need a candidate Unique Key on the
three FKs and you'll end up with more work joining the tables later on.
Horses for courses.

It must be permitted, but it's not a critical feature

Sorry: that's the meaning of "must". Either the model has to model it or
it doesn't.

Your comments have been very helpful to clarify things and focus my
thinking.

Glad to help

All the best


Tim F