[ale] Database design

[Christopher Fowler]

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