Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Join Date
    Mar 2009
    Posts
    4

    db design/relationship/unkown no. of entries

    I've been reading a lot of books lately for a project website I'm working on, but I'm still a newb. I'm trying to create a recipe database, but not sure how I'm going to implement some of the feature I want. For example, I want users to be able to submit there own recipes, and then be able to search all the recipes available. I also want them to be able to create a personal menu and then extract a shopping list from that menu. I have a table for recipes, but when I get to ingredients column, I'm not sure what the best method is. I originally thought about making 10-20 columns called ing01-ing20, or something like that, but I feel like I'm over complicating it. Is there a more logical way to this. I thought about a many to many relationship but then I'd have to have all the possible ingredients predefined and members would have to select ing from a drop down menu or something similar, when submitting new recipes.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Toady00
    I originally thought about making 10-20 columns called ing01-ing20, or something like that, but I feel like I'm over complicating it. Is there a more logical way to this. I thought about a many to many relationship but then I'd have to have all the possible ingredients predefined and members would have to select ing from a drop down menu or something similar, when submitting new recipes.
    Look up normalisation, and in particular First Normal Form.
    You don't need to have all ingredients predefined for many to many relationships. However, having a list is useful. To save typing it all in, you can start off with a publicly available database, e.g. USDA Nutrient Database for Standard Reference, Release 18
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    I'm trying to create a recipe database
    Why not see how any of the existing recipe sites on the web ask users to input the ingredients - in fact why not just use an existing system rather than produce another one?

    PS A pull down list of ingredients would never work - too many ingredients.

  4. #4
    Join Date
    Mar 2009
    Posts
    4
    Quote Originally Posted by mike_bike_kite
    Why not see how any of the existing recipe sites on the web ask users to input the ingredients - in fact why not just use an existing system rather than produce another one?
    I haven't seen any other sites that can preform what I'm trying to do.

    PS A pull down list of ingredients would never work - too many ingredients.
    I kinda already ruled out the pull down menu, for that reason.

    Ok so if I want a recipe table with individual columns for each ingredient and I don't know how many ingredients a recipe is going to use, what would be the best approach?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Toady00
    Ok so if I want a recipe table with individual columns for each ingredient and I don't know how many ingredients a recipe is going to use, what would be the best approach?
    The best approach is to change what you want. Did you read my post? Apart from the poor design, where would you put the quantities?

    Personally, I would use pre-populated ingredient tables. Obviously, you can't put them all in a single drop down - you would need filters. You can then have attributes for the ingredients (e.g. nutritional information). It also prevents you having multiple recipes that use "cabbage", "cabage", "cabbige", "caabage" etc.. However, it depends on your needs.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2009
    Posts
    4
    Quote Originally Posted by pootle flump
    The best approach is to change what you want. Did you read my post? Apart from the poor design, where would you put the quantities?
    Yes I did read it. I just found an article that defined 1nf in a way I understood how it related back to my problem. So I should have a table for recipes, another for ingredients, and a lookup table that links recipes with ingredients. Is that correct? That seems so much easier than I was making it. I understood many to many but I guess not having any experience I didn't recognize how to use it. I'm still thinking about how to work in quantities.

    Personally, I would use pre-populated ingredient tables. Obviously, you can't put them all in a single drop down - you would need filters. You can then have attributes for the ingredients (e.g. nutritional information). It also prevents you having multiple recipes that use "cabbage", "cabage", "cabbige", "caabage" etc.. However, it depends on your needs.
    Yes this is the approach I've been thinking about, and I think it would work quite well with filters.

    I think that answers my question but if you think I've missed something or have any other advice let me know.
    Last edited by Toady00; 03-17-09 at 00:39.

  7. #7
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by Toady00
    I haven't seen any other sites that can preform what I'm trying to do.
    I tried google and came up with the following on the 1st page - they seem to offer most of what you want :

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Toady00
    I'm still thinking about how to work in quantities.
    they go into the "lookup" table

    that is actually an unfortunate name for it -- it's more often called an association or relationship or many-to-many table

    a lookup table is usually one which translates a code, for example the table which translates the 2-char state/prov code into the state/province name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Just to throw in more stuff to ponder, your units will vary (weight, volume, count etc) too. That should probably be in the ingredients table.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pootle flump
    your units will vary (weight, volume, count etc) too. That should probably be in the ingredients table.
    Quote Originally Posted by r937
    [quantities] ... they go into the "lookup" table
    Quote Originally Posted by pootle flump
    Personally, I would use pre-populated ingredient tables
    Quote Originally Posted by Toady00
    I think it would work quite well with filters.
    It looks like you're trying to normalise the English language and I'm guessing you'll find it difficult. You'll spend forever trying to collect all the ingredients in the world and then find that your drop down items are slow and unwieldy.

    Imagine just typing "225g celeriac" into a normal recipe system - easy isn't it. Now imagine a system that forces you to enter 225 into one field then use a pull down menu to select grams. Then a pull down menu to select vegetables which then reloads itself to allow you to select root vegetables which then reloads itself again so you can pick celeriac. Now repeat this process for each and every ingredient.

    Remember if your item isn't on the list then you'll need to contact the site owner. I'm a technical guy but I know I'd want to be able to just type in my ingredients without any hassles but what method do you think an average user (a housewife perhaps) would prefer?

    Could you not just stick with free text entry but then get the system to split the ingredients into a list and allow trusted users to assign each item in the list to be assigned to dairy, meat, vegetables etc - this would allow you to build the shopping list a little easier. These trusted users could also correct any misspellings.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by pootle flump
    To save typing it all in, you can start off with a publicly available database, e.g. USDA Nutrient Database for Standard Reference, Release 18
    It's a while since I looked at this but there are tens of thousands of items, categorised.

    I am not trying "normalise the English language". Not only is what I suggest NOT normalisation, my domain is significantly less ambitious than the English language.

    Quote Originally Posted by mike_bike_kite
    Imagine just typing "225g celeriac" into a normal recipe system
    "225g celleriac"
    "225g celariac"
    "225g seleriac"

    Now imagine searching for it. And don't forget to wrap your search term in wildcards front and back. Scaaaaaaaaaaaaan.

    The problem with the free text column you suggest is the same problem you hit with folksomonies.
    Quote Originally Posted by mike_bike_kite
    Now imagine a system that forces you to enter 225 into one field then use a pull down menu to select grams. Then a pull down menu to select vegetables which then reloads itself to allow you to select root vegetables which then reloads itself again so you can pick celeriac.
    That's your system, not the one I suggested.

    Quote Originally Posted by mike_bike_kite
    Remember if your item isn't on the list then you'll need to contact the site owner.
    See above
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Obviously, these are two very different options.

    Mine allows very efficient, precise & reliable searching, standardisation, additional attributes (for example macronutrients, which would allow macros to be calculated for each recipe). The cost is that it would take longer to enter the data.

    Your would be difficult & unreliable to search (see folksonomies again), it would be slow (index scans) unless you put in some aggregation table(s), not standardised (grams, ounces, pints, litres and no means to convert), no additional attributes. However, submitting would be easier.

    My experience of recipe sites (I've used a few) is that they are set up as you have said and are VERY frustrating to search. These sorts of social network sites tend to have 100+ plus users per contributor. Balancing the needs of the two groups is the decision the OP needs to make.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Jun 2007
    Location
    London
    Posts
    2,527
    Quote Originally Posted by pootle flump
    It's a while since I looked at this [USDA Nutrient Database] but there are tens of thousands of items, categorised.
    If you have that many items then you have to have some variety of hierarchy to get to the ingredient you want. If you have 20 items per hierarchy then you end up with at least 4 levels. Also the user would have to scroll each time when using 20 items in a list. If you just have 10 items per hierarchy then you don't need scrolling but you'd need more levels to wade through.

    Quote Originally Posted by pootle flump
    Now imagine searching for it. And don't forget to wrap your search term in wildcards front and back. Scaaaaaaaaaaaaan.
    Wading through the above levels sounds very slow to me and what's worse it would be my time being wasted rather than the database doing a scan. Luckily there are full text indexes which should would perfectly on this (assuming MySQL). I tried searching for celeriac on their site - misspellings don't work and the hierarchies didn't help either - c'est la vie When you actually get to do the search using your method then I'll agree that it will be quick - even quicker if the complexity of entry discourages users from actually entering any recipes

    Quote Originally Posted by pootle flump
    "225g celleriac"
    "225g celariac"
    "225g seleriac"
    Luckily we have sound like comparisons. If I was doing it then I've have new recipes approved by trusted users who could correct spelling.

    Quote Originally Posted by pootle flump
    My experience of recipe sites (I've used a few) is that they are set up as you have said and are VERY frustrating to search.
    What on earth were you searching for? would Google of been a better search tool? The main advantages of my (and every other successful recipe site) design would be that it's easy to build, easy to use and would match what 99% of users want. The technological tour de force you guys are suggesting would be a pain to build and a pain to use. What's worse is the features you're adding would only be wanted by 1% of your users.

    This is obviously becoming a religious argument so we're unlikely to sway each other. I was going to make a comparison to the space pen that NASA spent millions developing and that was capable of writing in 0g, and then point out how Russians simply used a pencil. Sadly the story isn't entirely accurate but the idea is there anyway.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Mike, are you assuming the users would scroll through big list of ingredients?
    If you had a table of ingredients, it is searchable, which makes it dead easy to find what you're looking for
    George
    Home | Blog

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Not religious, no. Just defending my design as you are yours. Ultimately, we all agree on a normalised design. Me - many to many structured data, you - one to many unstructured data.

    BTW, you can have either full text indexes or soundex, but not both (TMK).
    Quote Originally Posted by mike_bike_kite
    What on earth were you searching for? would Google of been a better search tool?
    I was searching for ingredients - e.g. "I've got some tripe - what recipes are there on here for that?"
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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