Database Design

I am working on defining three entities to track cookbook recipes. In the future, tables will be added to track recipes from web sites and homemade recipes. Do you agree with the entity definitions shown below? Here is some sample data:
Recipe Book Page
Brownies Desserts 14
Brownies Make It Quick 45


ENTITY DEFINITIONS
Cookbook Recipes: A set of instructions for preparing a dish from a given cookbook.
Cookbooks: The name of a book containing a set of instructions needed to prepare a given dish.
Cookbook Recipe Details: Allows the cook to track recipes and related information. It allows the cook to quickly track and
find information about recipes.


ADDITIONAL COMMENTS
There cannot be duplicate recipe names in a recipes table, although, as shown above, you can have a "brownies"
recipe from two different cookbooks.
Mark01Asked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
>> Should there be “cookbook recipes,” “website recipes,” and “homemade recipes” entities? <<

I don't believe so.  I believe this should just be recipe.  The original source of the recipe would just be an attribute.  If you really wanted to make the distinction of the source integral to the model, you could use a supertype of "recipe" of a subtype of the other types.  I don't believe that's really necessary here, but you might see future needs that I don't.


In your definitions, where appropriate, refer to other entities in the model to enhance understanding and avoid ambiguity.  For example, rather than:
  Cookbook Recipes: A set of instructions for preparing a dish from a given cookbook.
  Cookbooks: The name of a book containing a set of instructions needed to prepare a given dish.
I'd suggest something like:
  Recipe: A list of ingredients, and ingredient amounts, required to prepare a given dish.  A list of specific steps to prepare, combine and cook (if necessary) those ingredients.
  Cookbook: A book containing recipes.
In particular, the definition for one entity should not contain/embed the definition of another entity.  In the original version, if you change/enhance the definition of recipe, you have to change it within cookbook also.
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Couple of thoughts...

1.

Be careful asking homework-ish questions like this.  We'll help you learn concepts, we won't flat-out do homework for you, so asking questions like 'Do you agree with...' will be treated by many experts here as homework.   And also a reply like 'this isn't homework' generally gets laughed at around here.

2.

Cookbook Recipes:  What you described would be more like just 'recipe', as a single recipe can be in multiple cookbooks.
If done right there will likely be a single table for 'cookbook' and 'recipe', and a M:M table for Cookbook Recipes.

3.

>Cookbooks: The name of a book containing a set of instructions needed to prepare a given dish.
The 'a given dish' part is likely incorrect, as most cookbooks I know contain multiple recipes to prepare multiple dishes.

4.

>Cookbook Recipe Details
Spell out what 'details' means.

5.

>There cannot be duplicate recipe names in a recipes table
Okay, and this can best be accomplished with a unique constraint on Recipe.Name, but it might not be a bad idea to plan for having to accommodate this, and not implement the index.
0
 
plusone3055Commented:
JH

I actually have the book where this problem is in ;)
its just been modified a little bit here

from
TRecipes
TrecipieIngredients
TrecipeInstructions

the chapter is on 1NF

to Author.
heres a hint..

Connect the dots with  intrecipieID
and create indexes for your ingredients and instructions
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Mark01Author Commented:
@Jim Horn: This is not homework. Should there be “cookbook recipes,” “website recipes,” and “homemade recipes” entities?

@plusone3055: I did not get this design problem from a book. I do not understand your answer; it sounds like you are referring to a book.

The only book I am using to learn database design is the following:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands-/dp/0201752840/ref=sr_1_2?s=books&ie=UTF8&qid=1431613709&sr=1-2&keywords=database+design+for+mere+mortals
0
 
Jeffrey CoachmanMIS LiasonCommented:
Do you agree with the entity definitions shown below?
This is a very open-ended, vague question, ...the answer will vary form Expert to Expert.
It will also depend on any number of factors that are unknown to us.

Besides:
In the future, tables will be added to track recipes from web sites and homemade recipes.
What relevance doe this have here?

Are we advising on your current design or your "future" design?

I envision a lot more than three tables...

I recommend deleting this Q and reformulating the question form the beginning...
For example:
I am creating a database to help Chefs find recipes from various books.
The chef would like to_____________, and find_____________.....(etc)

JeffCoachman
0
 
Mark01Author Commented:
Jeffrey Coachman: I am using the advice from Michael Hernandez’s book about how to define entities. Here is the link to his book:
http://www.amazon.com/Database-Design-Mere-Mortals-Hands-/dp/0201752840/ref=sr_1_2?s=books&ie=UTF8&qid=1431613709&sr=1-2&keywords=database+design+for+mere+mortals

The database will track information about recipes from different sources. By sources, I mean books, websites and homemade recipes. What other facts do you need to know? The focus of this question is on the "recipes" table(s). I do not want advice on future design, just about "recipes."
0
 
Jeffrey CoachmanMIS LiasonCommented:
I still feel that the scope of this Q should be narrowed.

Do you agree with the entity definitions shown below?
It is not clear what a valid "answer" would be to a question this broad and undefined...

JeffCoachman
0
 
Mark01Author Commented:
Thank you, Jeffrey Coachman, Jim Horn, plusone3055 and ScottPletcher.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.