Design relational database with circular reference

Question

Imagine a bakery. I have a table for products which has a one to many relationship with a table for formulas which in turn has a one to many relationship with ingredients. The problem is I have a cake but one of the ingredients is frosting which itself is a product (you can't have the frosting just broken up into its individual pieces because the frosting is applied to many things and the amount of frosting on the cake may need to be changed). Whats the table design for a setup like this?

"Formula" is the same thing as "recipe", right? Can there be multiple formulas for the same product? I.e. if you make a cake in different ways, should it still be considered a same kid of cake? Your statement: _"products which has a one to many relationship with a table for formulas"_ seems to suggest so, but I'm unsure whether you are describing the fundamental property the data, or you are simply describing the current database design (which may diverge from how data should actually look like). — Branko Dimitrijevic, Sep 06 '12 at 10:20

score 3 · Answer 1 · answered Sep 06 '12 at 01:10

3

How about two columns in your ingredients table, one for actual ingredients and another that would point to some other recipes. Only one would be set for any given row.

Using two columns allows you to enforce referential integrity as well.

answered Sep 06 '12 at 01:10

Alain Collins

16,268
2
32
55

score 2 · Accepted Answer · edited May 23 '17 at 11:43

2

What you are looking for is actually a common database design pattern called Bill of Materials.

Here is a good blog about bill of materials.

In order to make it easier to work with the unlevel tree structure that such a design involves, you can use a physical implementation technique called visitation numbers, which I describe in some detail in my answer to this question.

edited May 23 '17 at 11:43

Community

1
1

answered Sep 06 '12 at 11:51

Joel Brown

14,123
4
52
64

Gene · Answer 3 · 2012-09-06T01:24:14.837

It seems you have only two objects: formulas and stuff. A formula describes stuff in terms of other stuff. An item of stuff may or may not be a product. This is a binary attribute: a third table. The scheme would be something like:

Stuff
-----
id : integer
name : string

FormulaPairs
------------
stuff_described_id : integer
ingredient_id : integer
amount : float

Product
-------
stuff_id : integer

Example queries:

Get all ids of ingredients of Apple Pie:

select ingredient_id from Stuff s inner join FormulaPairs p
where s.id == p.stuff_described_id and s.name == 'Apple Pie'

Get all names of products:

select name from Stuff s inner join Product p where s.id == p.stuff_id

Design relational database with circular reference

3 Answers3