Food Database

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

Relationships
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:
https://www.experts-exchange.com/questions/29181141/Food-Database.html


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.
EE_052820.mdb