How to model components for widgets

0
votes

I'm trying to model a database that tracks building complex recipes.

This is what I have so far.

  • Table A - recipes (Id, name, description, servings, time, temp ... etc)
  • Table B - Ingredients (raw materials)
  • Table C - Recipe Ingredients (fk recipeID, fk ingredient ID, amounts of ingredient per receipe)
  • Table D - Recipe Steps (fk recipe ID, steps to produce recipe)

The next stop is creating a model to create products by combining portions from multiple recipes to make 1 product. For instance, a pie consists of x amt of dough, y amt of filling and z amt of a topping.

Dough gets made in batches of 10x, Filling gets made in batches of 15y and topping gets made in batches of 20z

  • Table E Component (name)
  • Table F Component_pieces (fk recipesID, amt)

Some of these components are end products and can't really be combined to create a new product (like Apple Pie).

The problems is that other components can be combined with portions from the recipes and ingredients to make another, 3rd tier product. For instance, a 10" cake consists of 3 tiers of 10" cake (3 10" cake components) + X amt of filling (a recipe) + Y amt of Fondant (an ingredient).

How do I build a model that an represent both making a pie and making a complex cake without lots of duplication?

Thanks in advance.

1
sqldatabasemany-to-manyone-to-manydata-modeling

1 Answers

0
votes

The basic issue is that you have something similar to what's described as a Composite Pattern in object-oriented design patterns. That is, the various levels and nodes in a hierarchy can be populated with different types of things. A cake, for instance, can contain single ingredients such as flour, sugar, butter, etc.; or in the case of a layer cake it can contain cakes, which in turn contain ingredients themselves.

You have a couple options for the 3-layer cake problem, both of which are doable in a relational database because the levels won't go too deep. (This would be a no-brainer in a graph database.)

Option 1 - If the relationship between 10" cake and 3-layer cake is not important, then just omit the 10" cake as a component and store the ingredients. In other words, there is just a recipe and it's ingredients and you live with some duplication. The downside, of course, is that if the 10" cake recipe changes then you would have to assess whether to change the 3-layer cake, too.

Option 2 - If the relationship is important, then change Table C so that it refers to a more generalized component rather than an ingredient. That way, it can contain another recipe as well as ingredients. Instead of (fk recipeID and fk ingredientID), you would have (fk recipeID, fk componentTypeID, and componentID). The componentType would be one of two values--RECIPE or INGREDIENT. That is why componentID itself cannot be an fk - it might refer to one of two tables.