Re: [Nolug] Database design

On 2009-07-13 16:26, Chris Jones 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.

In DB lingo, this is a denormalized design. Very bad.

> 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.

Correct. That's why it's a Very 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?

Now your design is in 1NF, the First Normal Form.

I wonder, though, if an SQL database isn't the wrong technology for
a recipe database, since not all recipes are "mix it all together
and shove it in the oven".

Think, instead, of a hypercard-style solution. PyCard, maybe, which
(I think) also allows for full-text search.

-- 
Scooty Puff, Sr
The Doom-Bringer
___________________
Nolug mailing list
nolug@nolug.org