Hello and welcome to our community! Is this your first visit?
Register
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
    Location
    Orlando, FL
    Posts
    159
    Thanks
    4
    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
    Posts
    26,225
    Thanks
    80
    Thanked 4,456 Times in 4,421 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:
    Code:
    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, 03: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
    •