Table Desing Question

[Eddie's Bakery and Cafe']

Hi, I have a database design question. A friend and I were having lunch and
got into a database table discussion. Neither one of us is experienced in
database design or database application development. I showed my friend the
database application that I am developing for a bakery/cafe that I plan on
opening. My friend suggested I change my table design. This led into a
discussion about which design is more efficient, the one that I am using or
my friend’s design.

My application is similar to designing a table structure for a family tree.
There is a hierarchy of family members (great grandparent, grandparent,
parent, and children). There are only leaf nodes in the family tree for the
children, not the parents or grandparents.

Since I am opening a Bakery/Cafe, you can imagine my application is
analogous to the family tree example; but instead of people, it uses recipes.
The recipes are accessed through a tree similar to the family tree analogy.
For example, a "Chewy Chocolate Chip" Cookie recipe might be referenced by
defining a recipe path-like structure similar to:
Bakery/Cookies/Classic/Chocolate.

Using this idea, one might design four tables to access the cookie recipe
(tblBakery, tblCookies, tblClassic, tblChocolate). The cookie recipe is in
the “leaf†table (i.e., tblChewyChoclateChip) pointed to by the four tables.
In my design I don't actual use these table names, but I included them for
understanding.

I am using a table structure similar to: "tblMenu", "tblMenuCategory",
"tblRecipeType", "tblRecipeCategory" and for the actual recipes, I defined a
table called "tblRecipeNames". Each table has primary/foreign keys and the
relationships between the tables are a one to many (for one entry in the
“tblMenu†table, there can be several entries in the “tblMenuCategory†table
(i.e., Bread points to Rye, Pan, Whole Wheat, Artisan, Quick bread, etc)

My friend suggested that I use one table for all the recipe hierarchy
information, similar to a link list, and one table for the recipes
themselves. In my approach I defined five tables (four tables used for the
recipe path and one table used for the recipes). In my friend’s approach
there are only two tables in the design, one for the recipe path and one for
the recipes. My design steps through four tables in order to access the
recipes. In my friend’s design, the recipes are not limited to any nesting
order because the recipe hierarchy table is structured like a link-list. My
friend’s approach sounds simple enough; however, I’m concerned about table
normalization and the tables becoming too large and unmanageable.

If you have any suggestions or comments on this issue I would appreciate
your feedback.

Regards, Eddie

 

[taylormade]

Your Design would be the most normalized and as a result would scale better
than the flat file your friend proposed. However, you may seriously want to
consider a compromise. Four tables for your definition seems a little much.
As a rule of thumb one of the questions you want to ask when "normalizing
data" is "will this new structure compel me to query 2 or more tables to
answer most questions" if so then you may have gone a little too far. You
should not have all 2 column tables either.

You can still have a bread type field, or menu type field without creating a
whole table for it I would guess.

The reason you'd want to consolidate tables is because later it may become
actually confusing to look up something simple because you have to query 3
tables to do it.

 

[Eddie's Bakery and Cafe']

Your Design would be the most normalized and as a result would scale better
than the flat file your friend proposed. However, you may seriously want to
consider a compromise. Four tables for your definition seems a little much.
As a rule of thumb one of the questions you want to ask when "normalizing
data" is "will this new structure compel me to query 2 or more tables to
answer most questions" if so then you may have gone a little too far. You
should not have all 2 column tables either.

You can still have a bread type field, or menu type field without creating a
whole table for it I would guess.

The reason you'd want to consolidate tables is because later it may become
actually confusing to look up something simple because you have to query 3
tables to do it.

 

[Eddie's Bakery and Cafe']

Thanks for the response. Your answer makes a lot of sense.

 

[Pat Hartman]

In a family tree structure there is only ONE table for people. There is a
second table to define relationships. The relation table contains
personID1, personID2, and RelationshipType such as birthMother, birthFather,
Husband. Normally, you would only make the relationship to the immediate
parent. The grandparent and greatgrandparents can be derived by walking up
the tree and the children, grandchildren, etc can be derived by walking down
the tree.

The example you presented of your tables is not normalized. It would have
been better if you described your actual tables. However, if you in fact
have a table named tblCookie or tblChocolate, you are definitely heading in
the wrong direction. The implication of those table names is that you have
a table for each recipe type and major ingredient.

Basically you need three tables - tblRecipe, tblIngredient,
tblRecipeIngredient. tblRecipeIngredient is the relation table between
recipes and their ingredients. If you ever want to do any cost accounting,
you will need this information to figure out how much a recipe costs to
make. It will also help you if you want to find recipes that use large
amounts of a certain ingredient. Say you get a great deal on walnuts but
you have to use them before they get stale. You could look up recipes that
use 16 oz of walnuts and make those for your special this week. To expand
on this basic structure, you would probably want category tables that let
you group recipes into cookies, pies, cakes, etc. You may want a category
hierarchy so you can break pies down in to fruit, cream, custard, no bake,
etc.

 

[Eddie's Bakery and Cafe']

Hi Pat, Thanks for taking the question. No I don't have a table for every
recipe. I did a poor job in describing the design. I only have one table
for all the recipes and four other tables that describe that break everything
down into menus (main and category) and a recipe path (type and subtype).
Your feedback was very helpful.

Thanks Eddie