August 1, 2011 at 10:49 am
Hello Everyone
I am working on a small project for my wife & I. We have a cake business, and I am working on the website and database.
I have tried a couple different approaches to storing recipes in a database. I have thought about storing all the ingredients list in a list table, and use an associative table between the recipe and the ingredient table. I have thought about storing the ingredient list and steps in a single column, but then I would not be able to search all recipes for a certain ingredient. Don't like the idea of that. Most cakes do not have more then about 15 ingredients in the cake and the icing. And about the same number of steps to combine the ingredients for baking or mixing.
I am hoping that someone can guide me as to how I can store the ingredients, and the list of steps in the most efficient way. This is not my first database I have designed. But this one is rather tricky. I do want to be able to query the table to find all, of any certain ingredient. Such as any/all cakes with "strawberries", or any / all cakes that use "white chocolate", Things like that. I already have a category list table that each confection can be a part in.
I just assistance with storing the actual ingredients and steps for creating / baking. The list of steps must be able to be stored in a certain order. I am open to some suggestions.
Thank you in advance
Andrew SQLDBA
August 1, 2011 at 12:32 pm
http://office.microsoft.com/en-us/templates/recipe-collection-database-TC001018635.aspx
simple...but maybe "food" for thought 😀
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2011 at 1:33 pm
Recipes table, Steps table, Ingredients table, many-to-many between Steps and Ingredients. Some steps may not have ingredients, of course, unless you do a bill-of-materials style hierarchy for the ingredients and sub-assemblies of them. That's probably overkill for this, though.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2011 at 2:38 pm
I'd think GSquared prototype is right as far as the tables go, but I'd be concerned with data population after it was created.
I'd think that based on the way I've seen recipes on the internet, I'd do a one to many from Recipes to steps,and one to many from recipes to ingredients.
i don't see a relationship between ingredients and steps as being easy to cull out of a text string at all.
it would be a lot easier to parse existing recipes like that, since i don't think I've seen existing recipes that would be easy to parse a step (mix flour, eggs and the butter) into discrete relations.
typically you see ingredients on a single line for each item , and steps as *maybe* on a single line each, or a long descriptive paragraph.
Lowell
August 1, 2011 at 2:45 pm
I guess it depends on whether the recipes will be parsed automatically, or entered manually. Manually, an Ingredients table would allow a drop-down to be used, or a multi-select, which would be more efficient than typing in each one each time (after all, how many times are you going to end up typing "flour" and "water"?).
Automatically parsing them out from listings, it might be easier to enter them multiple times.
The usual considerations of data redundancy aren't really applicable. After all, if you end up with "water" in your Ingredients table once for every recipe, you'r not normalized, but you also aren't really worried about having to change the name of "water" multiple times, so it might not hurt anything.
On that point, if you decide that one recipe needs "water" changed to "wine", because it adds more flavor, having it denormalized might make that easier. Will make changing all your recipes with "butter" to "margarine" will be a more complex update, but not by much.
So designing it based on your primary data-entry method is a good idea.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 1, 2011 at 2:48 pm
http://www.sqlservercentral.com/Forums/Topic1048093-391-1.aspx#bm1048213
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
August 1, 2011 at 3:58 pm
Thank you Everyone for all the suggestions.
I am going to use the suggestions presented, and then load some data into the database via a .NET front-end, and see how well, or not well things go. The plan is to use this via part of our current website, but in a private section, so a stand alone app will not work well. I wish they would, because I really dislike re-inventing the wheel.
Yes, I did ask that same question a while back. But now I am fully motivated and planning to continue our site.
Thanks again everyone
Andrew SQLDBA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply