[ale] Database design

[George Carless]

On Mon, Nov 13, 2006 at 09:57:09AM -0500, Christopher Fowler 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:

[snip]

> 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

Don't key on ingredient_id+ingredient_id.  With this schema I would create a separate ID for the 
"ingredient map" as a primary key, and I think you need a field to identify the order of the step 
in the ingredient_map (see my comments below on direction_steps).  But I have to say that your 
schema looks a little odd to me -- when it comes to a recipe you're not just throwing your 
ingredients in all at once, and you're also typically going to have lots of different measures 
for your "amount" field (i.e. 1 package; 1/3 cup; 1 teaspoon)-- so I don't see why you would 
bother to have a separate field for "amount": you're not going to want to do queries on the 
amount field, and you won't be able to do any meaningful comparisons between different records, 
so why bother separating it?  (I know you're looking for a semi-complex example, but I think it's 
best to only follow practices that make sense in the real world; it's not at all uncommon for 
people to make overly complicated tables for no good reason.)

Also, if you are going to split off your ingredients into separate tables, I suppose it would 
make sense to get more relational on your directions -- rather than having "directions" as a 
field within the recipe table, you might want to have a direction_steps table. Something like:

recipe_id INTEGER NOT NULL,
step_id SMALLINT NOT NULL,
ingredient_id INTEGER NOT NULL,
directions SMALLTEXT,
PRIMARY KEY(recipe_id,step_id)

To my mind this would replace the recipe_ingredient_map and would do away with the directions 
field.  With this schema you have a relational structure where it makes sense - e.g. you could 
say "find me all recipes that contain eggs" (I don't think most people would typically want/need 
to say "find me all recipes that contain two eggs") - and you could also open up room for future 
expansion on the "steps" (e.g. a field to indicate a step could be skipped, or additional tables 
to allow people to comment on individual steps, etc.)

--George
--------------------------------------
George Carless ... kafka at antichri.st
Words are just dust in deserts of sound