CODEWITHSUNDEEP
mysqldatabase-design
bkdude
bkdude

Reputation: 189

Trying to avoid a circular reference

I'm setting up a new database and I'm coming across a circular reference and I'm unsure of the best way to design this.

so here it is in plain English.

I have a recipe. the recipe calls for ingredients. this is all fine and good except one of the ingredients for the recipe might require another recipe or it might be a store bought item.

for example: I want to make a salad (recipe)
It will have lettuce, carrots, onions (All good so far. just store bought ingredients.)
but I want to have ranch dressing. (uh oh, I can buy ranch dressing at the store but I have a recipe for ranch dressing that I want to use to make the ranch dressing.)

how would you go about creating the recipe and ingredients tables in the best fashion? I have a thought all ready on how to do it but It involved circular referencing and all the web sites say stay away from it.

Upvotes: 1

Views: 461

Answers (1)

Stephen Senkomago Musoke
Stephen Senkomago Musoke

Reputation: 3523

I would suggest the following tables:

a) Recipe - name, description, isingredient and other information

b) Ingredient - name, description, fromstore (yes or no value if its bought from a store), recipieid (the id of the recipe for this ingredient). This enables ingredients to have recipies

c) recipie_ingredients - receipieid, ingredientid (basically this ties a recipe to its ingredients)

The advice is correct, since you can end up in a circular loop form ingredients to recipies etc

UPDATE: I would recommend adding an isingridient column to recipe so that only recipes which are known to be ingredients are selectable to add to the Ingredient table. This can help reduce the chances of circular references

Upvotes: 2

Related Questions