"Multiple inheritance" (generalisation relationship) in data models

Question

For some school groupwork, I'm making a Generic Pizza Delivery Everything Management System, and got stumped on a problem during data modelling that I can't figure out without using at least several layers of ugly.

The restaurant keeps stock of ingredients and beverages/snacks/etc. (I'll refer to these as "drinks"). Drinks have a selling price. The app stores recipes for dishes - each dish has a collection of ingredients and the amount used, as well as its selling price. An order has a collection of recipes and drinks, but not ingredients - it doesn't make sense to sell flour directly.

From that I have the need for both a "stockable" generalisation between ingredients and drinks, and a "sellable" (or "menu item") generalisation between drinks and recipes; and I can't really recall ERD modelling allowing for multiple generalisations.

(Currently I just went with the menu item one and decided to let the stockable stuff have separate entity hierarchies, and handle it in application code. And am considering dropping the separate hierarchies and not express the ingredient/drink difference in the data model at all, instead using an "is ingredient" flag attribute.)

Is there something I'm missing that would let me model this without having to handle anything (or as little as possible) in application code?

For more fun if someone likes to do this kind of thing as a mental exercise / puzzle, dropped features involve:

• drinks coming in categories with different tax rates - think soft drinks and liquor being taxed differently; dishes also have a tax rate, but don't belong into a category
• it makes sense to sell /some/ of the ingredients - the toppings (for people who want extra bacon but no olives); we'd also have to store how many units of the topping is in a single "extra" serving.

Answer 1

Isn't there a simple solution where you have a table of ingredients or stockable items, which includes drinks and other ingredients that might be listed in their own right. None of these can be ordered directly.

You then have a table of menu items which are all a collection of ingredients / stockable items. A menu item collection may have only a single item - in the case of drinks this may always be the case, but could also include e.g. a slice of lemon.

Answer 2

SQL does not directly support the concept of inheritance or generalisation. The relationship between tables is only that of reference. So there is no "IS-A" mechanism.

You can use a foreign key to mean "belongs to" (as in "this drink belongs to the category of merchandise with 8% tax rate") or you can use a foreign key to mean "has one" (as in "this ingredient has one parent").

You can support separate hierarchies, allowing one item to belong to multiple trees, using the Adjacency Relation design.

Since this is a homework problem, I'll stop there and leave the rest for you. :-)