CODEWITHSUNDEEP
mysqldatabasedatabase-design
Casey
Casey

Reputation: 1981

Design relational database with circular reference

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?

Upvotes: 3

Views: 2299

Answers (3)

Joel Brown
Joel Brown

Reputation: 14388

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.

Upvotes: 2

Gene
Gene

Reputation: 46960

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

Upvotes: 1

Alain Collins
Alain Collins

Reputation: 16362

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.

Upvotes: 3

Related Questions