Design ER with complicated relationships between different tables I try to design a database and I am stuck dealing with a part of it. The problem is very scientific, so for simplicity, I will try to give you a simple representation that accurately matches the case.

Imagine we have an oven with many drawer trays (each can only be used once and then get stored), and each tray is split in a specific number of compartments. That is either:


8 compartments
2 compartments
no compartments (or 1 big if you like)


You can call it 3 types of trays if that's better for the database design, in case some other type becomes available in the future.

Now, we have projects (imagine persons) and each project can have 1 or more iterations (imagine bakes). Each iteration can have 1 or more samples (imagine cakes). Each iteration is assigned to 1 tray. Two or more iterations of different projects that run at the same time, can be assigned at the same tray. However, two iterations of the same project CANNOT be assigned at the same tray. Imagine a new iteration will come at a later date, so since the trays are single-use, the next iteration has to be assigned on a new tray.

Each sample can be stored in 1 or more compartments (we can divide a cake in smaller cakes and bake them in several compartments of the same tray). Also, 1 compartment can hold 1 or more samples from the same iteration of the same project (imagine we divide the cake to 4 parts, and we bake 2 parts in compartment_1 and 2 in compartment_2), or from an iteration of a different project.



So in other words, two or more projects can run in parallel, and their iterations can be assigned to one tray. Future iterations of either of the projects will be assigned in a different tray. Each sample and compartment can be related in any order, and we need to keep track of everything.

I have tried several things, but nothing feels good enough to post here as ER. My tables are the ones I have in bold: project, tray, sample, iteration, (not sure about compartment).

Since a project can use many trays, and a tray can host many projects, I use the iteration table as a join table. But now I have no clue where I should connect the sample.


A tray is hosting many samples, while 1 sample only uses 1 tray, so I could connect them 1-to-many. However, I also need to keep track of whether the sample is stored in 1 compartment or spread out on more. Then I also need to know which project each sample belongs to.
Similarly, if I relate sample to project, I will not have information about the tray relation to the sample.
An iteration may have 1 or more samples, while 1 sample can be in 1 iteration, so I could create a relationship sample-iteration. This way I will be able to know both the project and the tray each sample is related to. However, the compartment problem remains.


Sounds like a chasm trap, so a triangle relationship could be the way to go, but I am not experienced enough and I'm not sure how to design something like this.

To tackle the compartment problem, I could use a field in sample to store the compartment information and use the software to make sure the compartment values are as supposed to be. But not sure this is the right choice.

Any help to design an ER for this part of the project is highly appreciated.
Your idea of having iterations as a join table for trays and projects seems good.

samples are obviously strongly related to iterations, because one sample belongs an iteration, and an iteration contains several samples. Therefore I would make an iterations 1..n samples association.

Then, compartments belongs to a tray. But we can also see compartments as generic identifiers which, when associated with a tray, defines a specific "location" for a sample. We already know to which tray samples of an iteration are associated. Also, a compartment can contain several samples, and a sample can be spread into several compartments. So we need a join table between samples and compartments.
If we keep compartments as generic identifier (see them as slot numbers in a tray), we don't need relationship between trays and compartments. When filling a sample, we already know to which tray it belongs through the iteration. The user will just have to fill the compartment(s) where the sample is located.

You can also enforce the compartment choices for the user by setting a type to the tray, which let you know what compartments can be choosen.