Representing conditional relationships

[kadams0920]

I need to come up with a data model that represents product dependency. I have a table of products. I need to create another table that represents the depenencies of some of those products on other products. A product could be dependent on others in the following ways:

- on one other product
- on a product OR another product, etc
- on a product AND another AND another, etc
- on a product OR (another product and another product)

I'm not sure how to set up the relationships in this way. Can someone please suggest a solution to represent the OR and AND relationships or provide an example of a similar situation?

Many thanks.
Karyn

 

[SteveSchapel]

Karyn,

I don't understand what you mean by "dependent" in this context. Could you give some real-world examples of actual products to illustrate what this dependency is all about, and explain what the behaviour needs to be? Thanks.

 

[Steve R.]

You may want to look here for some thoughts. Database Answers I can't say if its good or bad since I have not used it.

Is this part of a manufacturing/assembly process of some type? If so you would need to define a series of steps and the products that are needed at each step of the production cycle. Not only that but the quantity of items needed at each step and the quantity remaining so that the next item will have "parts" available.

 

[The_Doc_Man]

This can become topologically very complex. How many layers of complexity are we looking at here?

Can we have thing #1 depending on (#2 AND #3) OR #4 OR #5 OR ( #6 AND (#7 OR #8))? Structurally, multi-layered, complex dependencies can be a real bear to sort out or link. So the very first thing that you need to define is your structural depth constraint. Without that information, you are not going to be able to proceed. Nor are we likely to be able to help very much.

 

[kadams0920]

I'm maintainign a database of software products, and some of the products rely on others to run. It's not so complicated as manufacturing and assembly. I'll try to explain a little better:

Products table:
abc
def
ghi
jkl
mno
pqr

let's say Product abc is dependent on products def and ghi for it to run. In this case product abc needs both of the other products for it to run. Another example of a dependency could be that jkl needs mno or pqr for it to run. It only needs one or the other, not both.

I want to set up another table that captures these dependencies, but I'm not sure how to differentiate the conditions - And and Or. The following table doesn't represent that. For jkl it seems like both products are needed. It needs to identify that its either one product OR the other. Any way to do that?

Dependency Table:
Dependent Product Parent Product
abc def
abc ghi
jkl mno
jkl pqr

Thanks.

 

[kadams0920]

Doc Man,

Yes, product #1 could be dependent on (#2 AND #3) OR #4. Not sure how much layering you need defined? Some products can be dependent on one other product, some can be dependent on multiple products. How do you suggest I document the structural depth constaints - by product?

 

[Steve R.]

Before even trying to do anything, graphically lay out the products and their dependencies on a BIG sheet of paper. Once that is done, you should be able to see how it all goes together.

 

[The_Doc_Man]

The problem is the structure you need to use to represent this kind of thing. A programmable logic controller can do this because it is interpretive and therefore can handle complex nesting. However, Access wants that in a different and less free-form structure.

Making a report out of it involves the possibility of recursion, which almost instantly rules out SQL-based queries because the math model on which SQL is based doesn't understand iteration and recursion. Only code can do that. And in turn, that kills the ability to use anything normally based on a recordset, which is implicitly formed from an SQL operation. Topologically, I can't see it.

You could do this in VBA code and create files that you build dynamically. But to do it straight-out from "ordinary" Access methods requires you to SEVERELY restrict your model. I can see one way to do single-layer grouping, but a second layer would be impossible without recursive calls.

Have your Assembly s/w table contain two kinds of entries. (A) single items and (B) packages. Use an IndividualItems table that actually enumerates the individual names of items independent of assembly membership. You will have an assembly members table that lists elements of an assembly.

The Assembly table has a name and a primary key. Maybe a flag that says Single or Package. That's all.

The AssemblyComponents table is a junction table that lists every IndividualItem member of the given assembly. PK of Assembly is one column, PK of IndividualItem table is another column. An assembly with one item that is self-contained has one record in the assembly components table. An assembly with two items has two records in the components table, and so on.

One last table - the prerequisites. This lists an element from the Assembly table and one or more OTHER MEMBERS OF THE SAME TABLE as prerequisites. A self-contained package has zero prerequisites. The logic of this table must be such that it is an OR table. I.e. if you have three listed prerequisites, you only need one of them to be complete in order to run the package.

Now to do the case for #1 requires (#2 AND #3) OR #4... (and #5 is a stand-alone case)

Assembly record #1 has component items SW#1
Assembly record #2 has component items SW#2 and SW#3
Assembly record #4 has component items SW#4
Assembly record #5 has component items SW#5

The prerequisite tables show:

Record for assembly #1 shows assembly #4
Record for assembly #1 shows assembly #2
No entry for assembly #5

Assembly #2 shows the AND of end-items #2 and #3. Prerequisite table shows that #1 requires assembly #4 or #1 requires assembly #2. Assembly #5 doesn't need anything.

You can continue to nest recursively by making assembles of assemblies. At some point you will get disgusted with the gyrations required, but this might do the job for you.

 

[kadams0920]

Wow, thanks Doc Man. Not sure I'm following it all after the first and second read through, but I'll give it a try.