troubleshooting Question

Food Database

Avatar of Mark01
Mark01Flag for United States of America asked on
DatabasesMicrosoft Access
6 Comments2 Solutions39 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros