: Hi folks. I've not run across a many-to-many that I couldn't break
: up before. If you have any insights I would sure appreciate it.
: Bottom line is this: how do you store something that by definition has
: repeating fields? An example is an assembly that is made up of 1-n
: number of components?
I'm not sure that this is a solution to your problem, since I
am only giving you a solution which is 'standard' in the
industry! But here it is ...
You need two tables, one of which will represent all ingredients,
and the other of which will represent the relationships between
those ingredients. The ingredients table will actually contain
end products (finished goods) in addition to actual ingredients
(sub-assemblies, components, and raw materials). The two tables
material_id (Primary Key)
parent_material_id (Primary Key)
child_material_id (Primary Key)
ingredient_sequence (Primary Key)
To represent a sponge cake and a glue you might have:
In the material table:
6,"Victoria Sponge Cake"
In the ingredient table:
The ingredient_sequence is provided in case you want to
include one ingredient multiple times in the same recipe.
It would be usual to create two indexes on the ingredient
table -- the primary key, and a secondary index which
consists of child_material_id, parent_material_id,
If you want to add another 'finished product', for example,
a 'double sponge cake', simply add another entry to the
material table, adn link its components to it using records
in the ingredient table, e.g.,
To the material table:
8,"Double sponge with icing"
To the ingredient table:
Instructions for making the finished products and sub-assemblies
would be attached to the material table, either as a sequence
of steps in another table, or as a text entry in the material
I hope this is of some help!