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?
想象一家面包店。我有一个产品表,它与一个公式的表有一对多的关系,而公式又与成分有一对多的关系。问题是我有一个蛋糕,但其中一个成分是结霜本身就是一种产品(你不能将糖霜分解成单独的碎片,因为结霜被应用于许多东西和蛋糕上的糖霜量可能需要改变)。桌面设计是什么样的设置?
3 个解决方案
#1
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.
为了更容易使用这种设计所涉及的不同级别的树结构,您可以使用称为访问数的物理实现技术,我在这个问题的答案中对此进行了详细描述。
#2
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.
使用两列还可以强制执行参照完整性。
#3
1
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:
获取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
#1
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.
为了更容易使用这种设计所涉及的不同级别的树结构,您可以使用称为访问数的物理实现技术,我在这个问题的答案中对此进行了详细描述。
#2
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.
使用两列还可以强制执行参照完整性。
#3
1
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:
获取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