Model data with many to many and overrides

Architecturedatabase-designmany-to-many

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).

Best Answer

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:

ToyModel

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:

SELECT ToyName
    , 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.

Related Question