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).
2
has been overridden by material_id3
, so that in the end this piece would consist of only two materials:1
and3
. The missing piece is how to specify that I don't want to see material_id2
.