0

I am designing a SQL database to hold recipe/pricing information. So far, it looks like this:

Current version

The idea is to track prices of various ingredients (brand specific) along with their prices so that the total cost of a given recipe can be calculated and tracked over time.

The issue is that not all recipes require brand specific ingredients, but some do. For example, a recipe for one cake may simply call for half a cup of milk, while a recipe for a second cake may call for half a cup of Alta Dena brand milk.

My plan to address this was to create an intermediate table, called Ingredient, with Ingredient: Item as a one to many relationship. In this set up, a recipe would call for a half cup of milk (an Ingredient) and this Ingredient could be mapped to multiple Items, say Alta Dena milk, Horizon milk, etc, each with a separate price.

That set up would look like this (excuse the poor mock up but I wanted just to get a quick visual.) There would need to be a separate IngredientToItem table as well, so think of the Ingredient table as just a placeholder for everything here.

Proposed change

This almost fixes the initial issue that not all recipes call for a specific brand, but it introduces a new problem: some recipes do call for a brand specific ingredient (Item.)

I am wondering what I should do now. Is there a common structure for this type of problem? Should I have two separate tables RecipeIngredients and RecipeItems, one each referring to the brand agnostic ingredients and the brand specific items? Should I add a nullable column to the RecipeIngredients table to refer to a specific Item where necessary?

ahh_real_numbers
  • 464
  • 3
  • 14
  • 1
    Does this answer your question? [How can you represent inheritance in a database?](https://stackoverflow.com/questions/3579079/how-can-you-represent-inheritance-in-a-database) – philipxy Jan 01 '22 at 18:05
  • You appear to have a circular foreign reference here: A Recipe can have multiple Items (represented by RecipeIngredients) but each of those relationships has a Unit. Somehow or other you also have multiple ItemPrice for each Item which also has a UnitPrice. So perhaps your schema anyway needs cleaning up, although I'm not entirely sure how – Charlieface Jan 01 '22 at 19:01
  • @philipxy I don't think this is an example of inheritance. Unlike insurance policies, where each unique policy can be classified as an auto or home policy, here we have ingredients, of which there are many different items that match each individual ingredient. A recipe is made up of ingredients, but sometimes the ingredient must be item specific. Maybe I'm just not seeing how the question you've linked matches mine though. – ahh_real_numbers Jan 02 '22 at 00:13
  • @Charlieface Rows in the Unit table are obviously measurements like grams, ounces, etc. Each RecipeIngredient needs a Unit and Quantity to make sense (example 3.5 grams.) Similarly, for pricing Items you also need a Unit and Quantity, otherwise the given price will be meaningless. Hopefully it makes sense. – ahh_real_numbers Jan 02 '22 at 00:17
  • The question uses the term "inheritance" but the question & answer are about subtyping, and you have different types of ingredients, in the sense that they have some different properties but are still all ingredients. – philipxy Jan 02 '22 at 00:20
  • You would have `Ingredient` and a subtype of `BrandedIngredient` (which has the same primary key, foreign-keyed to `Ingredient`). Then `RecipeIngredient` is foreign-keyed to `Ingredient` (*not* `BrandedIngredient`) and that means you now have a 1:0-1 subtype/supertype relationship, with `Recipe` in a many:many reltaionship with the supertype `Ingredient` – Charlieface Jan 02 '22 at 00:37
  • @philipxy But I detail above the issue I see with setting up Ingredient--Item as a parent--child relationship. How can the RecipeIngredient table specify an Ingredient and also (sometimes) an Item (child of Ingredient)? – ahh_real_numbers Jan 02 '22 at 00:41
  • @Charlieface Except that it means you will have, say, "Milk" appearing multiple times in the "Ingredient" table, which violates the primary key. Otherwise the PK for an "Ingredient" won't refer to a BrandedIngredient specifically. – ahh_real_numbers Jan 02 '22 at 00:45
  • Because `Ingredient` and `BrandedIngredient` have exactly the same primary key (enforced by FK), `RecipeIngredient` always refers to a row which is either only in the former or is in both tables. Arguably they *are* different ingredients, because one is branded, the other not. Although in your particular instance, perhaps consider a schema where you always have a "Generic" branded ingredient, so that you can refer to that if necessary – Charlieface Jan 02 '22 at 01:01
  • @Charlieface I get what you are saying but if I have five different brands of milk then that means I have six rows (five plus a generic) in the ingredient table where the ingredient name is "Milk". Since this is the only distinguishing column in that table, it seems like this would lead to problems. Plus, it seems like if I were to go this route, I might as well just create one table, with columns for ingredient and a nullable brand column. That would accomplish the same thing and would be simpler, no? – ahh_real_numbers Jan 02 '22 at 04:35
  • @Charlieface Ahh! Except the above won't work either! I want to be able to join to the items table in order to get all relevant items for a given ingredient when the brand does not matter, that way I can compare prices. It should be a one to many relationship on the one hand but I need a way to build into the recipe cases where an ingredient is brand specific. – ahh_real_numbers Jan 02 '22 at 04:48
  • 1
    Yes, the nullable column is another option. You could top that off with a foreign key to `BrandedIngredient` as mentioned, this ensures there is actually an available ingredient with that brand. Something like this https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=7d7c7644f59c1db057983ba87c149786 Technically speaking it is slightly denormalized I think, but looks OK. One downside is you can't have both a branded and not branded of the same ingredient on the same recipe, but hopefully that isn;t an issue – Charlieface Jan 02 '22 at 05:53
  • @Charlieface this makes sense. I think I have enough to start experimenting now. Thanks for the help. – ahh_real_numbers Jan 03 '22 at 18:29
  • @Charlieface Rearranging between designs for subtyping is not DB normalization or denormalization & doesn't involve the problems/anomalies they involve. Normalization replaces a variable by others that natural join back to it. Equivalently, it replaces a variable holding the rows that satisfy a condition that is a conjunction of conditions by variables each holding the rows satisfying one of the conditions. However some subtyping rearrangements involve natural outer join. – philipxy Jan 03 '22 at 23:43
  • Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. Please clarify via edits, not comments. – philipxy Jan 03 '22 at 23:43
  • @philipxy Depends what level of normalization you refer to. I don't think it conforms to BCNF but it does conform EKNF, arguably a more useful normal form – Charlieface Jan 04 '22 at 02:07
  • @Charlieface It does not matter, what I wrote applies to all normalization to higher NFs. – philipxy Jan 04 '22 at 02:15

0 Answers0