Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    Mar 2010
    Orlando, FL
    Thanked 8 Times in 8 Posts

    Question Table Structuring question

    Let's say I want to store recipes in my database and I also want to store the ingredients for each recipe as well. What's the best way to structure my tables? Should I have one table just for recipes and another table just for ingredients and just use a foreign key between the two? Or, should each new recipe get its own table for ingredients, that way there isn't one table with a list of thousands of ingredients.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Thanked 4,738 Times in 4,700 Posts
    Tough call.

    Suppose you have 10,000 recipes. And ONE out of those 10,000 uses a particular ingredient.
    Seems like a pain to have to put that ingredient into the Ingredients table, doesn't it?

    On the other hand, maybe 25% of all recipes will have Flour as an ingredient. So clearly you would want to normalize for that ingredient.

    Purists would say *always* normalize. I might argue that one entry in Ingredients table should be "OTHER" and then you have the actual ingredient in the RecipeIngredients table.

    In other words:
    Table: Recipes
        recipeid INT PRIMARY KEY
        title VARCHAR(...)
        directions TEXT
    Table: Ingredients
        ingid INT PRIMARY KEY
        ingName VARCHAR( )
    Table: RecipeIngredients
        recipeid INT REFERENCES Recipes(recipeid)
        ingid INT REFERENCES Ingredients(ingid)
        qty ??? VARCHAR( ) ??? 
        otherIngredient VARCHAR( )
    So if an ingredient isn't in the ingredients table, maybe you put NULL inti ingid in RecipeIngredients table and then put the actual ingredient into otherIngredient.

    qty is interesting: Is it a number? Along with a units-of-measurement? Or do you just put text (e.g., "1/2 tsp") in there?

    I assume that your real DB is not for something as simple as recipes, so have fun interpreting the above in terms of your real problem.


    LinkBacks (?)

    1. 07-11-2014, 04:08 AM

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts