Re: [Nolug] Database design

Sounds like you're basically agreeing with my second idea... I
haven't messed with db design in a while, I've forgotten a lot of the
rules already.

On Mon, Jul 13, 2009 at 4:33 PM, B. Estrade<estrabd@gmail.com> wrote:
> Wouldn't it be "Aunt Sue's God Offel Cobbler"?
>
> Sorry, not helpful :)
>
> If  I am reading your question properly, you should probably do something like:
>
> tbl_recipe
>  id
>  name
>  description
>  vomit_factor
>
> tbl_ingredients // really more like a ingredients-to-recipe table
>  id
>  fk_recipe_id
>  measure
>  unit
>  comment
>
> Brett
>
> On Mon, Jul 13, 2009 at 04:26:45PM -0500, 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.
>>
>> 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
> ___________________
> Nolug mailing list
> nolug@nolug.org
>

-- 
Chris Jones
http://www.doomsdaytechnologies.com
___________________
Nolug mailing list
nolug@nolug.org