Help with complex (to me) relationships

[zibzab]

Hi, I’m having difficulties creating a database to catalog workflows. I’ll try and explain. The workflows are made up of various components (these individual components are in 1 table). The workflow is then in another table and the components are added via a join table and viewed in a portal. This way we can see all the individual elements that make the workflow. Also in the components layout there is a portal listing all the workflows the component is used in.

This is important so that I can see the possible knock on effects of changing any one component.

The problem is, that a workflow might contain another workflow as part of it.

What I need to be able to do is add existing workflows to new workflows. As well as this, I need to be able to see all the components in the workflow that has been added to the new workflow.

This is where I am stuck, I don’t know if the workflow table should have a self-join so that it can reference itself, and even if it did, how would I see the components which that workflow uses.

Help would be much appreciated – I’m stuck!

[bcooney]

It is a complicated data model.

Perhaps the discussion in this thread, and the links there will be helpful:

Inventory Thread

[zibzab]

Hi, yes it does appear to be complicated – thanks for answering.

I have been trawling through the forums for the last 3 days and found an example which sort of does what I want by using a single table with self joins.

The problem with this however is that I can only view the links one way. For example, if workflow B uses workflow A as part of it, I can see a list of components (comp a, b & c) used in workflow A within workflow B via a portal.

However, what I am struggling to display is a reverse of that chain. ie, if I go to the comp a record, I can see that it is used in workflow A, but not that it is used in workflow B (due to being in workflow A).

As soon as I have some basic structure I’ll post it.

thanks for your help

[zibzab]

Hi bcooney,

thanks for that thread – very helpful. I can now get an individual component to show each workflow it is connected to, or what other components it might use.

However, this only goes to one generation (parent and children). I can’t view in the portal relationships beyond that.

ie. My workflow is wf1 and it uses comp1, comp2 and wf2. wf1 is also used by wf3.

What I can’t see is the components that wf2 uses and also the fact that wf3 is used by wf4.

Do you know anyway of displaying these descendants / ancestors of a relationship in a portal.

Thanks for your help

[zibzab]

I have created a sort of work around to this – but it is not great. Basically a new portal for each generation down the line for parents and children.

This is a bit clumsy especially when relationships might go up or down 10 + levels.

Now that it is possible to view all generations (in separate portals), do you know a way of getting them to view in one?

[bcooney]

Please post a diagram (you can do it on a FM layout) of your structure.

Also, Søren Dyhr has posted many times answering inventory questions. He's very busy lately, otherwise I'm sure he'd contribute to this thread. However, you'll probably find some good posts of his by searching.

You can gather child data into a field in a parent using the List function.

[zibzab]

Hi, I’ve uploaded a diagram of the relationships.

I have used a list, but i need to have the ability to navigate to any of those records from within a portal, and my experience of lists would suggest that the list is static rather than dynamic (I could be using the wrong terminology there).

I’ll check out the threads you mention – thanks again for your help.

[bcooney]

I'd really prefer an ERD, not a shot of the relationship graph. Do you have time for that?

[zibzab]

Hi, in all honesty I have never made an official ERD. I think this this is what you are after. I have one main table and then a join table. This is a Many to Many relationship based on Jonathan Stark’s model on recursive data structures.

[bcooney]

It's been quite a while since I've attacked this mind-numbing data model. Give me a bit of time to refresh my memory.

Hey, anyone else up for this? Help is appreciated!

[zibzab]

Its amazing how the diagram looks simple, but it’s actually really complex!!!

[bcooney]

I know. I've reread J. Stark's blog on recursive structures (http://jonathanstark.com/recursive-data-structures.php) . Although he says,

"For the purpose of discussion, we are going to refer to products that are made up of other products as 'Assemblies', and products that are part of other objects as 'Components'. Of course, when a Component is made up of other Components, it would be correct to refer to it as both an Assembly and a Component, but we won't let that worry us for now."

I don't see where he addresses a Component made up from other Components. Do you?

[zibzab]

The article sort of mentions it. The Gould Water Pump has the screw and silicone gasket as components of it, and it (the water pump) is a component the Engine.

This child / parent thing is reasonably easy to do – some of the scripts mentioned in the article are hard to follow though!

However, his model will show the above in 2 portals (one for parent and one for child relationships).

What it wouldn’t show though (when viewing the Gould Water Pump record) is that the Engine belongs to the Ford Mustang (for example).

[RodSierra]

IMHO you are going into an area where relationships in this type structure will not yield a workable solution.

BOM structures along these lines need to be exploded via scripting into another table we call a product structure. A top level BOM in our systems may contain cost complete items, or sub assemblies, then at their level these sub assemblies may contain other sub assemblies or cost complete items, and so on. Via scripting these top level BOM's are exploded into a product structure. How many nested levels this explosion yields is based upon the attributes of the upper level items. They may be pull from stock or make to order, or fabricated. Assuming the product structure quantities yield one product unit, another table will generate a work order that reflects the total quantities of the exploded materials to produce the product. Trying to relate all nested BOM sub assemblies to themselves within each top level BOM will get you into a convoluted recursive mess that will grind your solution to a complete halt with a minimal number of top level products.

I guess not a direct answer to your question, but unless your are dealing with a very limited amount of products, and very simple BOM's, I would not recommend this type structure.

[bcooney]

Thanks, Rod, for chiming in.

[zibzab]

Hi RodSierra,

thanks your advice – do you have an example of a good bill of materials structure to point me in the right direction.

[RodSierra]

We have one table we call Items, this then is linked to a table called BOM's. Our Items table consists of all items, meaning products, parts, documents, labor, etc. This is our basic structure. If an item is cost complete (no bom) a flag indicates such. When the product structure is generated it looks at each item in the top level bom and if that item has a bom then it explodes that bom, checking each item in that sub level bom for other sub assemblies, and keeps digging until it reached the bottom level, then pulls back on level and goes to the next item. Simply speaking it reaches bottom and climbs back out to the top level. The scripting can be quite complex, but I hope you get the basic idea. The technique is more about expanding the data in a simple bom relationship than it is about the structure itself.

[zibzab]

Hi, thanks for all your help. I think I have enough to work with.