"Multiple inheritance" (generalization relation) in data models
For some school group work, I'm building a one-stop pizza delivery management system and keep running into a problem during data modeling that I can't figure out without using at least a few layers of ugly.
The restaurant stores ingredients, drinks / snacks, etc. (I will call them "drinks"). The drinks have a selling price. The app stores recipes for dishes - each dish contains a collection of ingredients and the amount used, as well as its selling price. The order contains a collection of recipes and drinks, but no ingredients - it doesn't make sense to sell flour directly.
Because of this, I need both a "regular" generalization between ingredients and drinks and a "sellable" (or "menu item") generalization between drinks and recipes; and I can't remember ERD modeling that allows multiple generics.
(Currently, I just went with a menu item and decided to leave the spare stuff with separate object hierarchies in stock and handle it in the application code. And I am considering dropping the individual hierarchies and not expressing the difference between the ingredient and the drink in the data at all, instead of using the attribute flag "is an ingredient".)
Is there something I am missing that will allow me to simulate this without having to handle anything (or as little as possible) in my application code?
For more fun, if anyone enjoys doing things like mental exercise / puzzle, features discarded include:
- beverages with different tax rates - consider that soft drinks and liquors are taxed differently; meals also have a tax rate but are not categorized.
- it makes sense to sell / some / of the ingredients - fillings (for people who want extra bacon, but no olives); we would also need to store how many refill units are in one "extra" service.
source to share
Isn't there a simple solution where you have an ingredient table or spare items that include drinks and other ingredients that can be listed on their own. None of these can be ordered directly.
Then you have a table of menu items, which is a collection of ingredients / supplies. There can only be one item in a collection of menu items - in the case of drinks, this can always be the case, but it can also include, for example, a slice of lemon.
source to share
SQL does not directly support the concept of inheritance or generalization. The relationship between tables is just a reference. Thus, there is no "IS-A" mechanism.
You can use a foreign key to mean "belongs" (for example, "this drink is categorized as a product with a tax rate of 8%"), or you can use a foreign key to mean "has one" (as in "this ingredient has one parent ").
You can maintain separate hierarchies by allowing one element to belong to multiple trees using the Adjacency Relation construct .
Since this is a home problem, I'll stop there and leave the rest for you. :-)
source to share