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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.