A many-to-many normalization challenge

A many-to-many normalization challenge

Post by Alan Sasse of Rankin Technolo » Mon, 30 Jan 1995 16:01:37



: 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.

 
 
 

A many-to-many normalization challenge

Post by Mark Ste » Wed, 01 Feb 1995 11:56:09


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"

 
 
 

1. A many-to-many normalization challenge

I think this can be solved in  a straightforward way with 3 tables:
RECIPES, INGREDIENTS and an association table (with a better
name than this :-) called something like WHERE_USED.  Each recipe
has some sort of identifier which is also a unique (or primary key),
ditto for ingredients.  The WHERE_USED table simply associates the
recipe keys with the ingredients keys (and could also store
additional info. like amount)  The RECIPES & INGREDIENTS foreign
keys in the WHERE_USED table will be components of a composite
key for WHERE_USED.

To pull out a recipe you simply
join the 3 tables on the recipeidientifier of interest;  or if you are
interested in ingredients you use the ingredient identifier as
the select criterion in the join.

Hope this helps.  (BTW:  if you are concerned about performance,
the 3-table joins should be ok if all the identifier columns are
indexed;  and if the key values are numbers, perf. will be even
better.)

--
   Vince Ventrone
   The MITRE Corporation       "...In my opinion, there's nothing
   Bedford, MA 01730           in this world beats a '52 Vincent

2. copy access query to another access database

3. Need SQL for ManyToMany

4. Tuning Informix and Irix

5. manyTomany Relationship

6. US-DE: Wilmington-Solid Oracle PL/SQL Developer w/ Shell

7. Need SQL for ManytoMany

8. NYC - Sybase Developers - DERIVATIVES TRADING - Very Hot Opportunity

9. Need SQL for ManyToMany

10. Sybase challenge...(but maybe general SQL challenge)

11. Normalization Questions

12. Normalization question