Very Computer

: 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
could be:

material:
    material_id (Primary Key)
    material_description

ingredient:
    parent_material_id (Primary Key)
    child_material_id (Primary Key)
    ingredient_sequence (Primary Key)
    ingredient_quantity
    unit_of_measure

To represent a sponge cake and a glue you might have:

In the material table:
    1,"Flour"
    2,"Sugar"
    3,"Water"
    4,"Salt"
    5,"Butter"
    6,"Victoria Sponge Cake"
    7,"Glue"

In the ingredient table:
    6,1,10,4,"oz"
    6,2,20,4,"oz"
    6,3,30,2,"tbl"
    6,5,40,4,"oz"
    7,1,10,10,"cup"
    7,3,20,5,"cup"
    7,4,30,3,"tsp"

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,
ingredient_sequence.

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"
    9,"Icing"
   10,"Icing powder"

To the ingredient table:
    8,6,10,2,"ea"
    8,9,20,1,"cup"
    9,10,10,1,"cup"
    9,3,20,.5,"cup"

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
table itself.

[snip]

: Ron

I hope this is of some help!

Andy King.

As I see it you have three options.

1) Do what you have done, ie

Quote:>Recipes table:
>RecipeNum  Desc      Ingredient#  IngredientDesc     etc.
>1          Cookies   1            Water
>1          Cookies   2            Oil
>2          Cake      2            Oil

I assume you are not going to leave the DESC fields in the Recipes table!

Quote:>As you see, "Oil" (incredient with a unique #2 in the ingredient table) is
>used in the cookies and cake recipe.  There is no unique key for these
>line entries.

Tne unique key is a compound key RecipeNum+Ingredient#, this is quite valid
for normalisation.

Quote:>To find out what ingredients are in the Cookies (with unique recipe# 1)
>I have to scan the recipes table and pull out all line items where the
>RecipeNum = 1.  This is slower than I'd like, yet I can't find a way to

This is normal, you would index on RecipeNum to speed this up. You could also
do "find me all the recipes that have Oil in them"

2) Store your ingredients in a comma delimited (or use another delimiter) text
field. Of course you will have to then write code to extract these and make
sensible use of them. Use you will compromise the  "find me all the recipes
that have Oil in them" query.

3) Use a database that is designed to handle this stuff, eg AREV, PICK, an
OODB.

Full normalisation is not a requirement of a good database design. A good
database design is one that satisfies all the user requirements the most
efficently.

Mark Stern

"Lifes to short too worry about speling and grammer"