[Nolug] Database design

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
Received on 07/13/09