I'm having some trouble modeling the database for this particular scenario in Postgres:

We have a set of toys that contain several pieces. Each piece is associated with one or more materials. All of these relationships are many to many, because a piece can be placed in many different toys, and a material can be associated with many different pieces.

Here's where I lack the knowledge on implementation. There's another many to many relationship, where a toy can be made by many factories, and a factory can manufacture many types of toys. In the general case, for a particular toy, we want to know what pieces it has, and which materials are associated with those pieces. However, there's a case where the material can be different for a given piece depending on the factory the toy is made in.

In short, there's a "default" case where we know that, for a given toy, we have a set of pieces, and for each of those pieces, we have a set of materials. But, for an arbitrary factory, the material associated with a piece is different.

The furthest I've gotten is putting this information in the piece_material junction table:

| piece_id  | material_id  | factory_id |
| 1         | 1            | <null>     |
| 1         | 2            | <null>     |
| 1         | 3            | 1          |

But this obviosuly doesn't work because I don't have information indicating which material is being replaced in a particular factory. I'm looking for a query that will get associated materials with a piece, and only the overrides if there are some present (i.e. in the last row of the above example, if it's overriding material 2, that would only get materials 1 and 3 back).

  • your bill of materials should consider different version of the same product. – McNets May 9 '17 at 19:50
  • Why should the query you mention return materials 1 and 3 and not 2 and 3? Having piece_id 1 with materials 1 and 2 (and factory_id <null>) mean that this piece is made of two materials? – joanolo May 9 '17 at 20:15
  • 1
    @joanolo this is an incomplete, hypothetical example where the data in the table is not representative of the information I'm seeking. I would need a way to specify that material_id 2 has been overridden by material_id 3, so that in the end this piece would consist of only two materials: 1 and 3. The missing piece is how to specify that I don't want to see material_id 2. – BrDaHa May 9 '17 at 20:56

This doesn't seem complex, just normal relationships, just introduce a "Part" table which represents the physical aspects of your "Piece" (I imagine the part for the toy, while it can be made of something different, has to have the same physical parameters - length/width etc). The design I can imagine from your description:


Note the "ToyDefaultPiece" table which would resolve which piece is used by default. I have added in some extra fields which I can imagine would exist in a Toy design to hopefully make it more clear what the entity represents.

In addition, I can imagine you would query Toys/Parts/Pieces with something like:

    , ToyPart
    , PartName
    , PartLength
    , COALESCE(def_pc.PieceID, pc.PieceID) as PieceID
    , COALESCE(def_F.FactoryID, pc.FactoryID) as FactoryID
    , COALESCE(def_F.FactoryName, pc.FactoryName) as FactoryName
    , COALESCE(def_M.MaterialName, pc.MaterialName) as MaterialName
FROM Toy t
INNER JOIN ToyPart tp ON t.ToyID = tp.ToyID
INNER JOIN Part p ON tp.PartID = p.PartID
INNER JOIN Piece pc ON p.PartID = pc.PartID
INNER JOIN Factory f ON pc.FactoryID = f.FactoryID
INNER JOIN Material m ON pc.MaterialID = m.MaterialID
LEFT OUTER JOIN ToyDefaultPiece def ON t.ToyID = def.ToyID 
    AND p.PartID = def.PartID
LEFT OUTER JOIN Piece def_pc ON def.PieceID = def_pc.PieceID
LEFT OUTER JOIN Factory def_F ON def_pc.FactoryID = def_F.FactoryID
LEFT OUTER JOIN Material def_M ON def_pc.MaterialID = def_M.MaterialID

Using the COALESCE functions will enable you to override pieces with values you store in the ToyDefaultPiece table.

  • I might be getting thrown off by the ToyDefaultPiece, I think from what I was describing, the table would be "PieceDefaultMaterial", right? The toy doesn't have a default piece, but the piece has default materials. Or would that still be the way to model default materials for a piece? How would the query look? – BrDaHa May 9 '17 at 21:17
  • That makes sense now that the piece would have an associated factory ID... – BrDaHa May 9 '17 at 21:20
  • I think you will find the Toy does have a default piece because, as you said, a piece can be used in many toys. So while a single toy may use one default type of piece, another may use a different default piece. I can imagine this being the case with electric toys vs toys intended for water use - a screw may default to being made from plastic in one (due to buoyancy) and stainless steel in the other (for rigidity, or some other quality). – blobbles May 9 '17 at 21:32
  • Also added how I imagine a query to be assembled to get all toy pieces, overriding with the default. – blobbles May 9 '17 at 21:32
  • Note in my given example of electric vs water use toy - both pieces may be the same Part - i.e. a screw with a specific length, width, thread size etc – blobbles May 9 '17 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.