Database 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

 

[Alex Ivanov]

Eddie,

If I wrote the database I would create three main tables: Category, Recipe,
and CategoryRecipe
leaving Type aside, because I don't see a principal difference between Type
and Category in your design.
You can put in Category table anything you want and associate that category
to any recipe in Recipe table
by means of cross table CategoryRecipe. This design would not impose any
limitations to number of
categories and you can add another category at any time and associate some
of existing recipes to this
category on top to any other categories it already belongs.

If you really like the tree structure, then you can add another table, ie
CategoryCategory (or SubCategory),
which would link the Category table to itself. For example, if you have a
Category "Bread" with ID = 1 and a
subcategory "French Bread" with ID = 2 and "Gingerbread" with ID = 3 then
you would have the following
entries in CategoryCategory table
Category SubCategory
-----------------------
1 2
1 3

However, I am not in a bakery business and may not see something that is
obvious to you.
Which design you accept it is up to you and which design is better is
largely a matter of personal preferences.
Choose the one that will best suite your needs. I don't expect this database
grow large enough to impose any
performance issues, but again, I may be wrong...

HTH

 

[Eddie's Bakery and Cafe']

Hi Alex, thanks for taking the question. If I understand you correctly, the
tables Category and Recipe have a "one-to-one" relationship and the
CategoryRecipe table is used for cross referencing the two tables. If this
is incorrect please let me know.

Thanks, Eddie

 

[Larry Linson]

If I drew a correct conclusion from "tblChewyChoclateChip", you envision
having a separate table for each recipe. That would be a violation of
relational table design, and not a good idea. What would be a good idea is
to put the recipes as separate records into a single tblRecipes. This has
been suggested by others, but I am not sure their suggestion was clear about
the inherent badness of using a table's name (see above) as data, and having
all the overhead of using separate tables for each recipe.

Larry Linson
Microsoft Access MVP

 

[Eddie's Bakery and Cafe']

Hi Larry, Thanks for taking the question. Apparently I was not clear, I am
only using five tables in my application, not one table for each recipe. In
my application I have only one table for all the recipes (tblRecipeName) and
the other four tables are used to reference the recipe names using a
hierarchy scheme. My example of tblChewChocolateCookie was intended to be
used as an example.

Thanks, Eddie

 

[Alex Ivanov]

Hi Alex, thanks for taking the question. If I understand you correctly,

the
tables Category and Recipe have a "one-to-one" relationship and the
CategoryRecipe table is used for cross referencing the two tables. If
this
is incorrect please let me know.

Not exactly the case. Category and Recipe tables have many-to-many
relationship
by means CategoryRecipe cross table, that is Category and CategoryRecipe
have one-to-many relationship and Recipe and CategoryRecipe also have
one-to-many
relationship. Tis way you can associate any recipe with any number of
categories and
vice versa, any category to any number of recipes.

For example, you put an "Yummy Ice Cream Cake" in two categories,
"Cake" and "Ice Cream". In table terms it would look like:
Table Category:
CategoryID CategoryName
101 Cake
152 Bread
202 Ice Cream

Table Recipe
RecipeID RecipeName Description
55 "Yummy Ice Cream Cake" "whatever you want"
56 "Best Vanilla Ice Cream"
57 "Grammas' Birthday Cake"

Table CategoryRecipe
RecID CategoryID RecipeID
10 101 55
11 202 55
12 101 57
13 202 56

As you can see we have two items fitting into catgory Ice Cream,
but only one of them fits also into the Cake category.
Now if you want to get a list of all possible ice cream cakes,
you may write a query like this one:

SELECT RecipeName FROM Recipe r
INNER JOIN CategoryRecipe cr
ON cr.RecipeID=r.RecipeID
WHERE cr.CategoryID IN (101,202)

Normally you cold build parameters string (101,202) in this case
by selecting appropriate categoris from a listbox, combobox,
or check boxes.

HTH

 

[Eddie's Bakery and Cafe']

Thanks Alex, this makes everything very clear. I appreciate you taking time
to answer this question.

Regards, Eddie

 

[Alex Ivanov]

You're welcome.
BTW, I want to point out that I made a mistake in query sample
before anybody else noticed it ,
as it was, it would return all cakes and all ice creams, not what
I stated in the post.
The better query would look like this:

select RecipeName from Recipe
where RecipeID in
(
select RecopeID from categoryrecipe
group by RecipeID
Having Count(RecipeID)>=2
where categoryid in (101,202)
)