Solved

Entities for Food Database

Posted on 2015-01-05
15
246 Views
Last Modified: 2015-01-05
I am working on defining the entities for a food database. Please confirm the design.

This question is divided into three sections. Section 1 contains the database mission statement and various definitions. Section 2 contains the definition of the entities. Section 3 contains the sample data for each entity and sample reports.

SECTION 1
The purpose of the database is to maintain the data used to track the dates on which meals, snacks, etc. were made as well as track detailed information about the planning and preparation of dishes.

I do not know if the Missing Ingredients, Recipe Ingredients and Snacks entities are correctly defined. No business rules have been defined yet.


Definitions

Dish: A dish in gastronomy is a specific food preparation, a "distinct article or variety of food", with cooking finished, and ready to eat, or be served. A "dish" may be served on tableware, or may be eaten out of hand; but breads are generally not called "dishes."

Recipe: Instructions for preparing a dish are called recipes. Some dishes, for example vanilla ice cream with fudge sauce, rarely have their own recipes (and are not found in most cookbooks), as they are made by simply combining two ready to eat preparations of foods.


Course: A course is a component of a meal. The main course usually follows the entrée ("entry") course. In the United States and parts of Canada, it may in fact be called "entrée."

Dessert: The sweet course eaten at the end of a meal.

Snack: A snack is a small amount of food eaten between meals.

Open in new window


SECTION 2

Entity: Recipes
Description: A set of instructions for preparing a particular dish. A recipe table is needed to keep track of the unique instructions needed to prepare a given dish.

Attributes:
Recipe ID -- An arbitrary unique sequential number assigned to every recipe.


Entity: Cooking Methods
Description: The method used to prepare a given recipe.

Attributes:
Cooking Method ID -- An arbitrary unique sequential number assigned to every cooking method.
Cooking Method -- A cooking method.



Entity: Recipe Cooking Method
Description: Every recipe calls for a particular cooking method. Allows the cook to plan ahead for a variety dishes using different cooking methods.

Attributes:
Cooking Method ID -- An arbitrary unique sequential number assigned to every cooking method.
Recipe ID --An arbitrary unique sequential number assigned to every recipe.



Entity: Common Recipe Comments
Description: Frequently used comments that are associated with one or more recipes.

Attributes:
Common Recipe Comment ID -- An arbitrary unique sequential number assigned to every recipe comment.
Common Recipe Comment -- A common recipe comment.
Recipe ID -- An arbitrary unique sequential number assigned to every recipe.



Entity: Ingredients
Description: Any of the foods or substances that are combined to make a particular dish.
Attributes:
Ingredient ID -- An arbitrary unique sequential number assigned to every ingredient.
Ingredient -- An ingredient.



Entity: Ingredient Comments
Description: Unique comments pertaining to modifying one of more of the ingredients called for to make a particular dish. These unique comments help the cook plan what dishes to make and help to plan what ingredients to purchase.

Attributes:
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.
Ingredient ID -- An arbitrary unique sequential number assigned to every ingredient.
Ingredient Comment  -- A unique comments pertaining to modifying one of more of the ingredients.



Entity: Missing Ingredients
Description: The ingredients needed to make a recipe that are not available. This information will be used to prepare a shopping list.

Attributes:
Recipe ID -- An arbitrary unique sequential number assigned to every recipe.
Ingredient ID -- An arbitrary unique sequential number assigned to every ingredient.



Entity: Recipe Ingredients
Description: The ingredients called for in a recipe to make a dish.

Attributes:
Ingredient ID -- Identifier that specifies a particular ingredient.
Recipe ID -- An arbitrary unique sequential number assigned to every recipe.



Entity: Types
Description:  Describes the particular types of food associated with a given dish.

Attributes:
Type ID -- An arbitrary unique sequential number assigned to every type.
Type -- The particular type of food associated with a given dish.



Entity: Dishes
Description:  A dish is a specific food preparation that is ready to eat, or be served.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Dish -- A specific food preparation that is ready to eat, or be served.



Entity: Dish Types
Description:  Associates the type or category of food with the dish. 

Attributes:
Type ID -- An arbitrary unique sequential number assigned to every type.
Dish ID -- An arbitrary unique sequential number assigned to every dish.



Entity: Recipe Dishes
Description: The recipe that corresponds to a dish. 
It is important for the cook to be able to quickly find the instructions needed to prepare a given dish.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Recipe ID -- An arbitrary unique sequential number assigned to every recipe.


Entity: Desserts
Description: The sweet course eaten at the end of a meal. Attributes:
Dessert ID -- An arbitrary unique sequential number assigned to every dessert.
Dessert -- The sweet course eaten at the end of a meal.



Entity: Dessert Dishes
Description: A dessert is a type of dish, which is a specific food preparation. The cook uses this information to plan which dishes to make.

Attributes:
Dessert ID -- An arbitrary unique sequential number assigned to every dessert.
Dish ID -- An arbitrary unique sequential number assigned to every dish.



Entity: Snacks
Description: A small amount of food eaten between meals.
Attributes:

Snack ID -- An arbitrary unique sequential number assigned to every snack.
Snack -- A small amount of food eaten between meals.



Entity: Snack Dishes
Description: A snack is a type of dish, which is a specific food preparation.

Attributes:
Snack ID -- An arbitrary unique sequential number assigned to every snack.
Dish ID -- An arbitrary unique sequential number assigned to every dish.



Entity: Meals
Description:  A meal is an eating occasion that takes place at a certain time and includes specific, prepared food, or the food eaten on that occasion.

Attributes:
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Meal -- An eating occasion that takes place at a certain time and includes specific, prepared food.




Entity: Planned Dishes
Description: The dishes that are being planned to make. This information helps the cook plan ahead, even months in advance.
Attributes:
Planned Dish ID --An arbitrary unique sequential number assigned to every planned dish.
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Month and Year -- The planned month and year that the dish will be made.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.



Entity: Planned Dish Dates
Description: The date on which the dish being planned for will be made. This information helps the cook plan to make a particular dish on a given date.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Date -- The date on which the dish is being planned to be made.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.



Entity: Actual Dish Dates
Description: The actual date on which a particular dish was made.

Attributes:
Dish ID -- An arbitrary unique sequential number assigned to every dish.
Date -- The date on which the dish was made.
Meal ID -- An arbitrary unique sequential number assigned to every meal.
Ingredient Comment ID -- An arbitrary unique sequential number assigned to every ingredient comment.

Open in new window


SECTION 3

Here is the sample data for each entity pasted from an Excel spreadsheet.

Recipes								
								
Recipe								
Lazy Man's Lemon Sole								
Baked Macaroni								
Chicken and Barley Soup								
Seafood-Rice Casserole								
Sweet and Spicy Chicken with Rice								
								
								
Common Recipe Comments								
								
Recipe					Comment			
Baked Macaroni					Only a few ingredients			
Chicken and Barley Soup					Two simple steps			
Seafood-Rice Casserole					Only a few ingredients			
Sweet and Spicy Chicken with Rice					Two simple steps			
								
								
Ingredients								
								
Ingredient								
Apples								
Sole								
Pasta								
Barley								
								
								
Ingredient Comments								
								
Ingredient					Comment			
Apples					Use home-baked apple slices instead of canned apples			
Bean					Substitute canned red kidney beans with another kind of bean.			
Broccoli					Calls for  frozen broccoli. Use canned mixed vegetables instead.			
Squash					Use leftover squash.			
								
								
Recipe Ingredients								
								
Recipe					Ingredient			
Apple Cinnamon Quesadillas					Apple			
Bean Burgers					Red kidney bean			
Crustless Vegetable Quiche					Broccoli			
Pesto Pasta					Squash			
								
								
Missing ingredients								
								
Recipe					Ingredient			
Lazy Man's Lemon Sole					Sole			
Baked Macaroni					Pasta			
Chicken and Barley Soup					Barley			
								
								
Cooking Methods								
								
Method								
Crockpot								
Oven								
Skillet								
								
								
Recipe Cooking Methods								
								
Method		Recipe						
Crockpot		Apple Cinnamon Quesadillas						
Oven		Bean Burgers						
Skillet		Crustless Vegetable Quiche						
Skillet		Pesto Pasta						
								
								
Meals								
								
Meal								
Breakfast								
Lunch								
Dinner								
								
								
Dishes								
								
Dish								
Lazy Man's Lemon Sole								
Baked Macaroni								
Chicken and Barley Soup								
Seafood-Rice Casserole								
Sweet and Spicy Chicken with Rice								
								
								
Types								
								
Type								
Pasta								
Seafood								
								
								
Dish Types								
								
Dish					Type			
Lazy Man's Lemon Sole					Seafood			
Baked Macaroni					Pasta			
Chicken and Barley Soup					Poultry			
Seafood-Rice Casserole					Seafood			
								
								
Recipe Dishes								
								
Dish					Recipe			
Lazy Man's Lemon Sole					Lazy Man's Lemon Sole			
Baked Macaroni					Baked Macaroni			
Chicken and Barley Soup					Chicken and Barley Soup			
Seafood-Rice Casserole					Seafood-Rice Casserole			
								
								
Desserts								
								
Dessert								
Apple Cinnamon Quesadillas								
Pound Cake								
Pumpkin Pudding								
Chocolate Oatmeal Squares								
								
								
Dessert Dishes								
								
Dessert					Dish			
Apple Cinnamon Quesadillas					Apple Cinnamon Quesadillas			
Pound Cake					Pound Cake			
Pumpkin Pudding					Pumpkin Pudding			
Chocolate Oatmeal Squares					Chocolate Oatmeal Squares			
								
								
Snacks								
								
Snack								
Celery #1								
Yam #1								
Peanut butter #1								
Rice #1								
								
								
Snack Dishes								
								
Snack					Dish			
Celery #1					Celery #1			
Yam #1					Yam #1			
Peanut butter #1					Peanut butter #1			
Rice #1					Rice #1			
								
								
Planned Dishes								
								
Dish					Date	Meal		Ingredient Comment
Lazy Man's Lemon Sole					Jan. 2015	Dinner		Use home-baked apple slices instead of canned apples
Baked Macaroni					Feb. 2015	Dinner		Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup					Mar. 2015	Dinner		Calls for  frozen broccoli. Use canned mixed vegetables instead.
								
								
Planned Dishes Dates								
								
Dish					Date	Meal		Ingredient Comment
Lazy Man's Lemon Sole					1/14/2015	Dinner		Use home-baked apple slices instead of canned apples
Baked Macaroni					2/20/2015	Dinner		Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup					3/5/2015	Dinner		Calls for  frozen broccoli. Use canned mixed vegetables instead.
								
								
Actual Dish Dates								
								
Dish					Date	Meal		Ingredient Comment
Lazy Man's Lemon Sole					1/17/2015	Dinner		Use home-baked apple slices instead of canned apples
Baked Macaroni					2/20/2015	Dinner		Substitute canned red kidney beans with another kind of bean.
Chicken and Barley Soup					3/15/2015	Dinner		Calls for  frozen broccoli. Use canned mixed vegetables instead.

Open in new window


The Excel file containing the sample data for each entity pasted above is attached.

SAMPLE REPORTS

Crackers and Cheese #1
Ingredients:
Cheese
Crackers

1-14-2014
Snack: crackers and cheese

1-14-2014
Dinner: Lasagna casserole
Dessert: Cheesecake
SampleData.xlsx
0
Comment
Question by:Mark01
  • 5
  • 4
  • 2
  • +3
15 Comments
 
LVL 29

Assisted Solution

by:MikeOM_DBA
MikeOM_DBA earned 70 total points
ID: 40531879
Check out this model, It may help: DATA MODEL FOR CATERING BUSINESSES.
;)
0
 

Author Comment

by:Mark01
ID: 40531898
Hi Jim,
This is not a homework assignment or any kind of assignment for class/school. I am still learning about relational database design so I will wait to find out if anyone submits comments about the proposed definition of the entities.
0
 

Author Comment

by:Mark01
ID: 40531922
Hi MikeOM_DBA,

I appreciate the ERD. That ERD as well as most others only deals with a “Recipes” entity. One of my concerns is about the definition of a “Dish” “Dessert” and “Snack.” It sounds like a Dish includes a Snack.

Do you agree with the following definitions:
(1)
Entity: Snacks
Description: A small amount of food eaten between meals.

(2)
Entity: Desserts
Description: The sweet course eaten at the end of a meal.

(3)
Dish: A dish in gastronomy is a specific food preparation, a "distinct article or variety of food", with cooking finished, and ready to eat, or be served. A "dish" may be served on tableware, or may be eaten out of hand; but breads are generally not called "dishes."
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 40531955
“Dish” “Dessert” and “Snack.” are attributes of a recipe.
0
 

Author Comment

by:Mark01
ID: 40531992
Are Seafood, Soup, Pasta, and Poultry attributes of a recipe?
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 160 total points
ID: 40532000
I've looked at the ERDs in the link provided by Mike and haven't found them to be useful.

As Jim said, the question is too broad so I didn't spend a lot of time with it but it looks like you are taking attributes and making them entities.  For example, snacks and desserts are dishes.  They are not entities in their own right.  The dish entity needs an attribute named Course and Course would be Entrée,  Appetizer, Dessert, snack, etc.  However since many dishes can be served in different courses, you would probably use a many-to-many relationship rather than 1-many so Course wouldn't be an attribute in the dish table but would be placed in a junction table instead.  A missing ingredient is something you discover when you match your ingredients list to your pantry.  It doesn't exist except in that context although you could match an entire meal plan with the pantry to come up with a shopping list.  But, the shopping list is not a static table, it is an output report or spreadsheet and is created anew each time you need to print it.  

You would define a Course table to give each entry a "name" and to describe how it is used and assign a sequence number so that when you print out the menu for a meal, the courses are printed in the desired order.  Then as you are defining a meal, you would assign a dish to a course.  Course in this case doesn't even need to be one of the defined courses.  So, although sorbet is usually thought of as a dessert, in meals with many courses, it is interspersed to cleanse the pallet.  Or, if you are emulating the European model, the salad course comes at the end of the meal rather than at the beginning as in the US.

The sample spreadsheet you included will be extremely difficult to import if that was your intention.  Ingredients are not tied to recipes nor are cooking methods meals and dishes.  I'm really not sure what you can actually pull out of it except for recipe names.

I also can't tell the intention of the app.  Are you intending to be able to print recipes or just do meal planning?
0
 

Author Comment

by:Mark01
ID: 40532055
The intention is to eventually do detailed ingredient planning, preparation tasks and meal planning. One of my goals of this question is to get feedback about the basic required entities.
I probably should post one or two more questions focusing on Seafood, Soup, Pasta, and Poultry and make sure the entities are properly defined. A dish entity may not be required.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 40 total points
ID: 40532066
I agree with Jim and Pat...

This is far to broad a topic to be covered on a site such as this.

The site is really not designed to have this broad of a focus:
Please confirm the design

We would need to be intimately familiar with *all* aspects of this database to even begin to make any meaningful suggestions, ...
Even then, ...it will be nearly impossible to "confirm" the entire design have set forth here.

What are you looking for here exactly?
If this statement is true:
I am still learning about relational database design
...then a database of this level of complexity is not a good place to start...

We could all sit here and spout out "advice", ...but it is not clear what you are expecting here as an "Answer"

JeffCoachman
0
 
LVL 69

Accepted Solution

by:
ScottPletcher earned 230 total points
ID: 40532280
Have to get a little technical here.

A "Dish" is an Entity.  It's also a special type of Entity known as a "Super-Type".  A "Dish" will consist of one of the following "Sub-Types":

Soup
Appetizer
Entrée
Desert
...

You don't necessarily need a separate table for each sub-type; I suspect you won't here, but can't be sure yet.  In some cases sub-types each have separate tables of their own as well.  But, from a proper design perspective, it's important that you recognize up front that you have a specific relation of super-type and sub-type.

[For another example of a super-type / sub-type design, look at sys.objects / sys.tables|sys.foreign_keys|....]


Also, based on your description, you need to add another Entity:
"Bread"
since it is not considered a "Dish".
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 40532292
Mark,

I am working on defining the entities for a food database. Please confirm the design.

Inasmuch as I read the question: yes, it's a design for a food-based database.  As others have tried to explain, this question is way, way, way beyond the scope that can be easily answered by posting short text blocks on a web site. I strongly urge you to delete this question and post a new one with a much smaller scope and better-worded question.  

Otherwise my best advice would be to buy some time from a seasoned database developer who can work with you one-on-one to vet the project in a way that makes sense.
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 230 total points
ID: 40532299
Other thoughts:

"Planned Dishes", for example, should NOT have a made-up identity.  It has a meaningful and useful pk combination:
Meal ID
Course ID
Recipe ID

"Cooking Method" seems sufficient; I don't see a need for "Recipe Cooking Method".
0
 

Author Closing Comment

by:Mark01
ID: 40532365
Thank you, Jim Horn, MikeOM_DBA, PatHartman, Jeffrey Coachman, ScottPletcher and Jason C. Levine.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40532529
>> However since many dishes can be served in different courses, you would probably use a many-to-many relationship rather than 1-many so Course wouldn't be an attribute in the dish table but would be placed in a junction table instead. <<

I agree.  A "Dish" has sub-types/categories like "Entrée", "Soup", etc..  But a specific course in a specific meal could serve a dish "out of category", for example, a soup as a dessert or a salad as an Entrée.  That just confirms that Meal and Course are separate entities as well.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40532537
Btw, user beware: the student ERD above is exactly that -- student level.  It's incomplete and not fully normalized (among other things).
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now