We help IT Professionals succeed at work.
Get Started

Food Database

Mark01 asked
Last Modified: 2020-05-29
I am trying to define the entities for an Access database that will track foods (dishes), such as deserts and cakes.  Please help me define the entities.

I am not dealing with composite primary composite keys at this time because I am just testing a simple table structure to understand how to define the entities. I am using a test  database because I am having difficulty defining the entities. A copy of a test database is attached.

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

baked goods-cake: 1:M
cake-baked goods: 1:1

deserts-baked goods: 1:1
baked goods-deserts: 1:1

cake-dish: 1:1
dish-cake: 1:M

Based on the comments below, it sounds like there should be "type" and "category" entities.

Here are some comments about SQL Server design from my last question about this topic.

Scott Pletcher (5-06-20)

A "dish" -- i.e., a fully completed recipe -- will fall into, let's say, a "type".  The "type" may be "pastry", "sauce", "cream", "cake", etc..  

The "type" might then further fall into a "category": "baked good", "meat", "fish", etc..  

There are just some possibilities to spur your thoughts.

Ultimately I think you'll find you have a supertype / subtype pattern here.  Most easily explained by an example.  Take SQL Server itself.  An object is a supertype, with a subtype of table OR view OR procedure etc..  That is, every entry is an object, which all share specific characteristics, but then each object is one, and only one, subtype, for which specific other characteristics apply, but only for that subtype.

So, for example, "deserts" could be a supertype, and "cake", "pie", "pastry" might be subtypes.

Again, these are all just possibilities to help you think about the data itself.
Brian Crowe (5-06-20):
There are many different ways to handle this depending on how much functionality you want to support.  You could use a tiered self-referencing "type" table or you could use something more free-form like tagging which just associates 0 - n labels with a given dish.  Either way you are likely going to want to support many types/tags per dish through a many-to-many joining table.
Mark Wills (5-06-20):

You mentioned flow : "flow: cake recipe #1-->batter-->cake-->baked goods.". Which is almost back to front as far as I am concerned (in terms of data).

You start with the end result of "cake recipe #1" and then try a flow from there. Except, you would typically start at "Baked Goods" and drill into "cake" then find a list of cakes, yielding a recipe, which might need "batter".

Pie would be an attribute of Recipe, and avoid having to add Recipes to a "Pie" table. After all it is a Type of recipe. Similarly, Cake is a type (rather [category]) of Baked Goods (given your data flow above).

Previous food database question:

Here is some information about the database.

The purpose of the food database is to maintain recipe, food preparation and food storage data.

This will be an Access database intended for a home kitchen. It is not intended for commercial use.

Database Objectives
Keep track of dishes made.
Keep track of recipe ingredients.
Keep track of frozen and refrigerated dishes.
Keep track of frozen and refrigerated ingredients.
Keep track of tasks performed to prepare dishes.
Watch Question
IT Supervisor
Distinguished Expert 2020
This problem has been solved!
Unlock 2 Answers and 6 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE