I have a relationship where my product table contains raw materials, sub-assemblies, assemblies, etc. They are all called "products" with unique productid. I don't think it fits the BOM because a BOM consists of materials merging to create sub assemblies, merged to create assemblies, etc.
我有一个关系,我的产品表包含原材料,子组件,组件等。它们都被称为具有独特产品的“产品”。我不认为它适合BOM,因为BOM包含合并以创建子装配的材料,合并以创建装配等。
This means that one raw material batch can be used on many sub-assemblies, and one sub-assembly can have multiple raw material batch. That new sub-assembly can be used on many assemblies, and vice-versa. This is a many-to-many relationship, but at infinite levels so joining two tables would not work (like materials, assemblies tables if I had only these two levels).
这意味着可以在许多子组件上使用一个原材料批,并且一个子组件可以具有多个原材料批。新的子组件可用于许多组件,反之亦然。这是一个多对多关系,但是在无限级别,因此连接两个表将不起作用(如材料,程序集表,如果我只有这两个级别)。
Many parents, many childs inside same table, and the hirearchical model seems to handle normal hirearchy.
许多父母,同桌内的许多孩子以及雇佣制模式似乎都处理正常的雇佣关系。
Could I create a "family" table with id_Parent and id_Child both keys to the same identifier in the products table? This way I could repeat one parent for multiple childs or one child for multiple parents.
我可以使用id_Parent和id_Child创建一个“family”表,这两个键都是products表中相同的标识符吗?通过这种方式,我可以为多个孩子重复一个父母,或为多个父母重复一个孩子。
Is there an other/better method to do this?
有没有其他/更好的方法来做到这一点?
1 个解决方案
#1
3
Actually I think this is a BOM, though the table design doesn't dictate that. Whether a product is a "material", "assembly" or "sub-assembly" is defined simply by whether it has any children (though you could denormalise that fact to the product table itself if query performance is an issue).
实际上我认为这是一个BOM,虽然表设计没有规定。产品是否是“材料”,“装配”或“子装配”仅仅是由它是否有任何子项来定义(尽管如果查询性能是个问题,您可以将该事实反规范到产品表本身)。
I've implemented this with two tables:
我用两个表实现了这个:
Product (product ID etc)
Components (Parent product, child product ID, quantity)
This works really well. In SQL Server you can fairly easily query it with a Common Table Expression to establish the "leaves" of any given BOM.
这非常有效。在SQL Server中,您可以使用公用表表达式轻松查询它,以建立任何给定BOM的“叶子”。
Careful with your use of the components table, though. In this example the products and components tables together give you a pattern to build a kit/assembly. For later reporting you need to effectively take a copy (or "instance" if you prefer) of the BOM and store that elsewhere. I'd probably have Product and Component describe the BOM for an assembly, and ProductItem and ComponentItem describe an actual assembled assembly representing a real item once assembly has taken place.
但是,请谨慎使用组件表。在此示例中,产品和组件表一起为您提供了构建工具包/组件的模式。对于以后的报告,您需要有效地获取BOM的副本(或“实例”,如果您愿意)并将其存储在其他地方。我可能有产品和组件描述组件的BOM,而ProductItem和ComponentItem描述了一旦组装发生就代表真实项目的实际组装组件。
You could say Product/Component are as to Class as ProductItem/ComponentItem are to object in that the second is an instance of the first.
您可以说产品/组件与Class类似,因为ProductItem / ComponentItem是对象,因为第二个是第一个的实例。
#1
3
Actually I think this is a BOM, though the table design doesn't dictate that. Whether a product is a "material", "assembly" or "sub-assembly" is defined simply by whether it has any children (though you could denormalise that fact to the product table itself if query performance is an issue).
实际上我认为这是一个BOM,虽然表设计没有规定。产品是否是“材料”,“装配”或“子装配”仅仅是由它是否有任何子项来定义(尽管如果查询性能是个问题,您可以将该事实反规范到产品表本身)。
I've implemented this with two tables:
我用两个表实现了这个:
Product (product ID etc)
Components (Parent product, child product ID, quantity)
This works really well. In SQL Server you can fairly easily query it with a Common Table Expression to establish the "leaves" of any given BOM.
这非常有效。在SQL Server中,您可以使用公用表表达式轻松查询它,以建立任何给定BOM的“叶子”。
Careful with your use of the components table, though. In this example the products and components tables together give you a pattern to build a kit/assembly. For later reporting you need to effectively take a copy (or "instance" if you prefer) of the BOM and store that elsewhere. I'd probably have Product and Component describe the BOM for an assembly, and ProductItem and ComponentItem describe an actual assembled assembly representing a real item once assembly has taken place.
但是,请谨慎使用组件表。在此示例中,产品和组件表一起为您提供了构建工具包/组件的模式。对于以后的报告,您需要有效地获取BOM的副本(或“实例”,如果您愿意)并将其存储在其他地方。我可能有产品和组件描述组件的BOM,而ProductItem和ComponentItem描述了一旦组装发生就代表真实项目的实际组装组件。
You could say Product/Component are as to Class as ProductItem/ComponentItem are to object in that the second is an instance of the first.
您可以说产品/组件与Class类似,因为ProductItem / ComponentItem是对象,因为第二个是第一个的实例。