Table Design Help

[Arg81]

Hello,

Just looking into a new project I've been assigned, the orginal app was a Lotus 97 Approach db!!


A "DC" has a "Recipe" assigned to it (only 1 recipe per DC).

A "Recipe" is made up of individual "Ingredients".

An "Ingredient" has an associated cost to it.

The "IngredientWeight" is not based at Ingredient level, instead it's set at the recipe level.


Now, I tried setting the following tables up:

DC
DCNumber (Pri), RecipeID, {other fields}

Recipe
RecipeID (Pri), IngredientID (Pri), IngredientWeight

Ingredient
IngredientID (Pri), IngredientName, IngredientCost


There I can link Ingredient to Recipe via IngredientID on a 1-to-many relationship (1@Ingredient, many@Recipe)

However, I can then not set a relationship up between Recipe and DC. I think it's because I have RecipeID and IngredientID set as the Primary Key on the Recipe Table.
I need to try and break this down into another table I think, just looking for pointers where it needs to be.

Regards,

 

[Arg81]

This can be ignored, I accidently posted the message after figuring out the problem.

I forgot there needs to be an ingredient weight table in there, and that solves the problem.

 

[cjard]

If the relationship from DC to recipe is 1:1 why arent they in the same table?

 

[Arg81]

Think I worded it wrong - a DC only has 1 recipe but a recipe can be in more than one DC. So it's a 1 to many oppose to a 1 to 1.

The way I got around this was to insert a "middle man" table. It relies on all the recipes existing in the recipe table, and then all the ingredients in the ingredients table.
I called it RecipeIngredients, and it then allows for multiple ingredients to be set to the 1 recipe.

 

[cjard]

Makes sense.. and of course, RecipeIngredients is where the ingredient weight is set... ?

 

[cjard]

Think I worded it wrong - a DC only has 1 recipe but a recipe can be in more than one DC. So it's a 1 to many oppose to a 1 to 1.

Just a thought... You dont need a middleman table to decompose a 1:M relationship. By adding one all you will do is create a 1:1:M relationship