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."
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).
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.
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
”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
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.