Re: [Nolug] Database design

Kevin Kreamer wrote:
> On Mon, Jul 13, 2009 at 17:26, Chris Jones <techmaster@gmail.com
> <mailto: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 <mailto: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

I think I'm missing a reason for the database structure being so complex.
I would just make one table with 4 columns (mostly just type of text)
and that's basically it. Of course for searches and such, one can play
with different indexing stuff.

My columns would be :
id, rcp_name, recipe, instructions

To further spice up the site, we add table for categories and we can
then link the categories table to the actual recipe table.

Petri

-- 
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
___________________
Nolug mailing list
nolug@nolug.org