Self Relationship

[pfro]

Hi all,
I run a manufacturing outfit and we do everything with filemaker since 15 years.
We produce cosmetics.
We have a product table which has a manyToMany self-join via a "components" table.
Because products are made out of Products.
So, for example, a Shampoo 300 ml, is made out of 300 ml of Shampoo, 1 Flask, 1 Label and 1/6 of a box (6 in a box).
The bulk Shampoo is made out of say 10 ingredients, which we store in another table, that we called Formulas (think of recipes, 1 kg of Shampoo has 800 g of Water, 100 g of LSS and 100g of Perfume, for example).

This whole system has worked well, but we found out, that after all, Formulas.. is actually a "Product" too.. And we think now, of eliminating this whole Formula (with FormulaItems) .
A PRODUCT.. if it is liquid has liquid components.. and a Product, that is measuered in Units has Components.

As a side kick.. It might all be too complicated to explain here.. Why would we want to change. Recently we had to comply with new regulations, and many ingredients, which we though where "pure", i.e. made out of ONE Ingredient, where actually made out of multiple Molecules. For starters.. for example all Acids.. are always in H2O, Water disolved.
So.. if you add 5g of Nitric Acid to a Shampoo.. ou actually add 2,5g of Water and 2.5g of Nitric disolved to the Formula.. So.. our Shampoo does not have 800g of Water, but 805g of Water.
We solved this, by creating TO's that go Product=Formula=FormulaItem=FormulaComponent. (FormulaComponent are the Ingredients that make Ingredients).
So.. When we tried to get rid of the whole FORMULA=FormulaItens TO's Group, substituting with Products=Components, we found that our sistem does not elegantly see may hierachy steps into subComponents.
What I am looking for, is the Calculation in a field in COMPONENTS, that connect Products<>Products, that permits to see the whole Line of Components and subComponents, with their respective Quantities, gives an error if I try to make an infinitive Loop (Connecting a Component to a Product, that contains the Product itself in its SubComponents)>

All this is very basic Recursive Database Design..
SOrry for the complex and a bit long question.. But people who have solved this problem, might understand it easily.

In the Car Factory Example;
A car is made of 4 wheels, a motor and a chassis and 20 screws.
The chassis is made of 4 doors
The door is made of 3 screws
The motor is made of 4 screws
How many screws are in a car?

Screw that.. I'going nuts..

[Malcolm]

It does get really complicated doesn't it.

I've worked with a few similar systems. The basics that you outline are broadly correct, products are made of products. I found it easiest to talk about recipes and ingredients. You might have a recipe for a bread roll: flour, water, salt, yeast. A recipe for a hamburger includes a hamburger patty in a bread roll. Both the patty and the bread roll are ingredients, but are also recipes. You can combine recipes into meals, e.g., hamburger, fries and a drink. And so on, and so on.

This is potentially an infinite series, though in practice, it ends up being limited. You will probably end up using a mix of dynamic and scripted operations. You can visualise any recipe easily because the ingredients are one step away (or two).

But to create a shopping list of all the components may take you through the same relationship loop multiple times. In addition, you'll be exploring the loop to different depths for different components, so you need some way to flatten the loop.

A fully normalised description is very easy. The basic "engine" is two tables. However, when you want to visualise this for an exploded diagram, or a shopping list, you need to have created a unique instance for each component. You might do this by assuming the the recipes are just templates. They don't become real until you generate a production run. At that point you are aggregating items from your stock-on-hand (with associated batch numbers) to convert the recipe ( ie, the description of the thing) into an actual thing. At that point, you are drawing down from your stock-on-hand, using your recipe template, to create new records which capture the batch number of the stock, the supplier, etc).

Now, instead of having one record called "water" in your ingredients table (which is referenced by dozens of recipes, and may have multiple uses within a single recipe. And which you cannot show more than once in a summary or sub-summary, even though you need it dozens of times), you will have dozens of actual records represented each instance of water from each component in your recipe. And while it sounds a bit silly when you are talking about liquids, if you were talking about semi-conductors, for instance, you will have part-numbers, serial numbers, and batch codes which all need to be traced. The concept is the same. And now, because you have dozens of actual records for each time the ingredient is used you can "flatten" the recipe by finding all the ingredients, sorting them by their position in the recipe hierarchy, and then you can easily generate subsummary, total, count, etc.

[pfro]

@Malcolm

It does get really complicated doesn't it.

I've worked with a few similar systems. The basics that you outline are broadly correct, products are made of products. I found it easiest to talk about recipes and ingredients. You might have a recipe for a bread roll: flour, water, salt, yeast. A recipe for a hamburger includes a hamburger patty in a bread roll. Both the patty and the bread roll are ingredients, but are also recipes. You can combine recipes into meals, e.g., hamburger, fries and a drink. And so on, and so on.

Yeah.. well analised. One can also, as an idea, call this Batch manufacturing and Repetitive Manufacturing

This is potentially an infinite series, though in practice, it ends up being limited. You will probably end up using a mix of dynamic and scripted operations. You can visualise any recipe easily because the ingredients are Ione step away (or two).
I look for a simple Multikey field, that visualizes any recipe with all its ingredients.. preferably without scripting

But to create a shopping list of all the components may take you through the same relationship loop multiple times. In addition, you'll be exploring the loop to different depths for different components, so you need some way to flatten the loop.
Yes!! What do I need? How is it done

A fully normalised description is very easy. The basic "engine" is two tables. However, when you want to visualise this for an exploded diagram, or a shopping list, you need to have created a unique instance for each component. You might do this by assuming the the recipes are just templates. They don't become real until you generate a production run. At that point you are aggregating items from your stock-on-hand (with associated batch numbers) to convert the recipe ( ie, the description of the thing) into an actual thing. At that point, you are drawing down from your stock-on-hand, using your recipe template, to create new records which capture the batch number of the stock, the supplier, etc).
When we go into production, we do just that.. e formula, or "recipe" becomes a OM, ManipulationOrder, and a Product (in units) becomes a OP ProductionOrder. They pull out of stock their components and ingredients and produce kg of Bulk and units of Product

Now, instead of having one record called "water" in your ingredients table (which is referenced by dozens of recipes, and may have multiple uses within a single recipe. And which you cannot show more than once in a summary or sub-summary, even though you need it dozens of times), you will have dozens of actual records represented each instance of water from each component in your recipe. And while it sounds a bit silly when you are talking about liquids, if you were talking about semi-conductors, for instance, you will have part-numbers, serial numbers, and batch codes which all need to be traced. The concept is the same. And now, because you have dozens of actual records for each time the ingredient is used you can "flatten" the recipe by finding all the ingredients, sorting them by their position in the recipe hierarchy, and then you can easily generate subsummary, total, count, etc.
How? .. do I easily flatten the recipe?

I tried to use remarks in you very nice and helpful answer. Thanks a lot..
I have not yet understood all you mentioned.

[bdbd]

I've been dealing with manufacturing and the type of problem-space you mention for over 20 years.

I found it most helpful to divide a produt's components list into two lists: a first-level components (FLC) list; a raw components (RC) list.

FLCs are the "visible" components that make up the product. Think of them like the ingredients list for food items. A FLC list is a mix of raw and compound components.

Compound components are things like molecules, finished goods, mixed ingredients, etc. They are a mix of other compound or raw components. Compound components may be made internally or externally (by a supplier, for example).

RCs are indivisible components from the perspective of your product. For example, a molecule is indivisible in food ingredients. Not so in chemistry. Note that components may be qualified. For example, it may be important to differentiate between 20°C water and 95°C water in a components list.

The RC list is a transformed FLC list where the compound components, their compound components and so on, have been changed to their raw components. This RC list is used for ordering, analysis, showing percentage of indivisible components and other purposes. The RC list always contains only one instance of a raw component.

A user defines a product by progressively editing its FLC list. Scripts update the RC list of a product each time the product's FLC list is edited or each time the RC list of any of the product's compound component is modified.

Here are a few things to note about this approach:

• Data is redundant by design in order to improve performance and to overcome issues with recursivity;
• Scripts copy or remove a compound component's RC list to / from the product's RC list when a user adds or removes a compound component to a product;
• Scripts recursively update the RC list of each product containing a compound component when a user modifies that compound component;
• Copying or removing may instead mean updating a quantity, as the RC list for a product always only contains one instance of each RC;
• Scripts navigate the FLC lists when performing recursive operations on products' RC lists.

Hope this helps and is a little clearer than mud!

[tonywhitelive]

You are heading in the right direction...
A single table for all item types (Simple, Bundle, Component, Bundle and Component) works well.

As you have indicated with your example, things can be made of things, that are made of things, that are made of things, etc...in other world a recursive structure.

Since an item can have many components and
an item can be a component of many bundles...
...you need a join table to resolve the many to many.
Components is a reasonably good name (see below).

A script can traverse the recursive structure to present reports on paper or screen that are flattened out into 2 dimensions, for example:

• 1. A
     A1
     A2
• 2. B
     B1
     B2
     B3
• 3. C

This is a layout from the item context showing an item that is both Bundle and Component

[pfro]

Hi Tony, Hi Bdbd,
first thanks for you great input.
Tony>
Yeah.. we have that, I attach our screenshot of the an item that has components, and is also a component.
There is no script needed, only a portal.

Now.. Lets say I would like to see the components of "SL intimo woman bulk". here in this view too..
What I want is to show quantities and info for more that FLC, (first level components) as bdbd calls them.

Does your system do that?
How.. by population some portal or some list or virtual list recursively?
Or in one swell swoop with a Multikey Field in the Components table?

Used in

made out of these 7 components