[ale] Database design

[Charles Shapiro]

Or maybe you could just add a "QTY" column to the ingredients table to
differentiate.

-- CHS

On 11/13/06, Christopher Fowler <cfowler at outpostsentinel.com> wrote:
>
> I'm giving a Perl DBI talk at Atlanta PM on Thursday.  I need to create
> a sample database design for the code but am at a stand still.  My idea
> is to create a Recipe database and I'm going to use this recipe in the
> talk:
>
> ----
> Mystery Pecan Pie
>
> Topping:
> 3 eggs
> 1/4 cup sugar
> 1 cup light corn syrup
> 1 teaspoon vanilla extract
>
> Pie:
> 1 (8-ounce) package cream cheese
> 1/3 cup sugar
> 1/4 teaspoon salt
> 1 teaspoon vanilla extract
> 1 egg
> 1 un-baked deep-dish (9-inch) pie shell
> 1 1/4 cups chopped pecans
>
> Preheat oven to 350 degrees F.
>
> Combine all topping ingredients in a medium bowl. Set aside.
>
> In a medium bowl, mix cream cheese, sugar, salt, vanilla, and egg
> until combined. Pour into pie shell. Top with chopped pecans.
> Pour topping over pecans. Bake for 46 minutes. Serve warm or at
> room temperature.
> -----
>
>
> Here is the schema:
>
> CREATE TABLE ingredient (
>   ingredient_id INTEGER(11) NOT NULL AUTO_INCREMENT,
>   name VARCHAR(20) NOT NULL,
>   description VARCHAR(250) NULL,
>   PRIMARY KEY(ingredient_id),
>   INDEX XAK1level(name)
> );
>
> CREATE TABLE recipe (
>   recipe_id INTEGER(11) NOT NULL AUTO_INCREMENT,
>   name VARCHAR(20) NOT NULL,
>   description VARCHAR(250) NULL,
>   directions MEDIUMTEXT NULL,
>   PRIMARY KEY(recipe_id),
>   INDEX XAK1level(name)
> );
>
> CREATE TABLE recipe_ingredient_map (
>   recipe_id INTEGER(11) NOT NULL,
>   ingredient_id INTEGER(11) NOT NULL,
>   amount VARCHAR(20) NOT NULL,
>   PRIMARY KEY(recipe_id, ingredient_id)
> );
>
>
> Based on this design the recipe can not have two rows in the
> recipe_ingredient_map that are the same ingredients.  In the example
> recipe eggs are used in the topping and the base.  So this schema will
> not work.  Maybe there is a more complex schema that will fit.  Maybe
> separate the recipe into parts and have those parts defined in another
> table.  I can _easily_ create 1 table with all the rows but I want to
> semi-complex example for the talk.  I'm tired of all books and etc using
> customer or employee examples.
>
> Chris
>
>
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale
>
-------------- next part --------------
An HTML attachment was scrubbed...