0

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)

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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Brandon
  • 187
  • 13

1 Answers1

0

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.

Community
  • 1
  • 1
Joel Brown
  • 14,123
  • 4
  • 52
  • 64
  • My only concern with a sequence is when looking for the Item you would have to write `select Itemid from ItemJunMaterial where matieralID = 1 and sequence =1 and materialid =2 and sequence = 2 etc..` but if that is the way to go then that's what I have to do – Brandon Sep 09 '16 at 18:27
  • @bme2010 Yes, you need to use the primary key when selecting out individual records. If that primary key is a composite then you need `and` in your `where` clause. Will you be doing that very much? How often will you be selecting the full list? In your 0NF structure, `Item (material1, material2, material3)` you get the whole list with a single select. In your semi-normalized structure, you also get the whole list by using `where materialid in (1,2,3)`. – Joel Brown Sep 09 '16 at 21:32