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