使用紧耦合数据规范化表 [英] Normalize a table with tightly coupled data

问题描述

我目前有一个表格，用于存储用于创建项目的材料

I currently have a table that stores the materials used to create an item

Item (material1, material2, material3)

最近添加了另一种材料.但是，我想对该表进行规范化，以便将来添加新材料时不需要将新列添加到数据库中.我把材料提取到下面的连接表中

Another material has recently been added. However, I want to normalize this table so that in the future, adding a new material won't need a new column to be added to the database. I extracted the materials into the following junction table

ItemJuncMaterial (id, itemId, materialid)

然而，问题在于材料的顺序很重要.所以这个连接表不允许我根据材料运行这个查询来获取项目

However, the catch is that the order of the materials matter. So this junction table won't allow me to run this query based on materials to get the item

select itemid from ItemJunMaterial where materialid in (1,2,3)

这可以返回使用 2,3,1 甚至可以使用两种材料 1,2 的项目.有没有更好的方法来拆分这个表来规范化它并使其更具动态性?

This can return items that use 2,3,1 or could even use two materials 1,2. Is there a better way to split up this table to normalize it and make it more dynamic?

推荐答案

您应该考虑物料清单"(BOM) 模式(请参阅 此处 或此处).BOM 的交集可以包括有关组成的额外信息，例如每个组件的数量.您可以轻松地在该交集中包含组件的顺序或优先级.

You should consider a "bill of materials" (BOM) pattern (see here or here). The intersection of a BOM can include extra information about the composition, such as quantity of each component. You could easily include the sequence or priority of the component in that intersection just as easily.

这篇关于使用紧耦合数据规范化表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！