[ale] Database design

Charles Shapiro hooterpincher at gmail.com
Mon Nov 13 10:51:06 EST 2006


Yeah, you make some good points.  I'm not really a DB designer -- just
tryin' to get the Quick Fix in.

-- CHS

On 11/13/06, George Carless <kafka at antichri.st> wrote:
>
> 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
> _______________________________________________
> Ale mailing list
> Ale at ale.org
> http://www.ale.org/mailman/listinfo/ale
>
-------------- next part --------------
An HTML attachment was scrubbed...




More information about the Ale mailing list