Re: [Nolug] Database design

On Mon, Jul 13, 2009 at 17:26, Chris Jones <techmaster@gmail.com> wrote:

> I might be working on a database-driven app pretty soon, but have a
> question on what might be the best approach for database design.
>
> Let's say you're doing something like a recipe database. You would
> have multiple recipes, obviously. I'm just trying to figure out the
> best way to do the table layout.
>
> Each recipe would look like this:
>
> Aunt Sue's God Awful Cobbler
> =======================
> 2 cups flour
> 3 eggs
> 2 cups sugar
> 3 lbs of tripe
>
> Mix all ingredients, throw in the oven at 400F for 30 minutes. Eat.
> Vomit.
>
>
> So, you are storing various types of information on each recipe, such
> as the recipe's name, the directions on how to make it, but then you
> have an ingredients list, which would essentially have to be a 2D
> array. It would store the # of units, the unit type, and what exactly
> the ingredient is...times however many ingredients that particular
> recipe would have. That's where a bigger part of the problem comes
> in, that each recipe could have 3 ingredients, or 20.
>
> I'm not sure if you should just have a ton of columns of data for your
> recipe table to accomodate for all the possible ingredients.
>
> My own wisdom of database design says that's probably a bad idea.
>
> So, should I just have an ingredients table that looks like this:
> (first column is an ID number corresponding to the recipe it is for)
>
> 1, 2, cups, flour
> 1, 3,, eggs
> 1, 2, cups, sugar
> 1, 3, lbs, tripe
> 2, 1, cups, flour
> 2, 3, tsp, vanilla
> 2, 2, cups, sugar
>
> And that is basically ALL ingredients of ALL recipes, then you just do
> a SELECT WHERE ID = 1 to pull all the items for recipe # 1.
>
> Is that the way to go for that sort of database? Or is there yet
> another way that would be even better?
> ___________________
> Nolug mailing list
> nolug@nolug.org
>

Your second idea is what you want to do, basically. You'll want each
ingredient as its own row in an recipe_ingredient table. For normalization,
you'll probably also want an ingredient table with an ID and an ingredient
name (1 => flour, 2 => sugar, 3 => vanilla, 4 => tripe, etc.) And a unit
table (1 => cups, 2=> tablespoons, 3 => metric tons, etc.).

The issue with all of this, of course, is that there are some recipes that
don't match this kinda of idealized recipe (say, for Grandma's gumbo). So,
the other course is to just store each recipe as a file, and do a text match
on the file contents, google-like.

Kevin

___________________
Nolug mailing list
nolug@nolug.org
Received on 07/13/09