Design ER with complicated relationships between different tables

database-design

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.

Best Answer

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.

Related Solutions

Constraint many to many table between two child tables

Why must the employees working on a project strictly belong to the same division that owns the project? That sounds more like a business rule that you can leave up to your application to enforce rather than something that requires DRI.

But let's say you need to enforce this using DRI. You said you have a many-to-many table associating employees with projects. Assuming you are using SQL Server, you could expand this table as follows:

CREATE TABLE dbo.Employee_Project (
     ProjectID            INT
   , ProjectDivisionID    INT

   , EmployeeID           INT
   , EmployeeDivisionID   INT

   , Hours                INT

   , CONSTRAINT FK_Project FOREIGN KEY (ProjectID, ProjectDivisionID) 
        REFRENCES dbo.Project(ProjectID, DivisionID)

   , CONSTRAINT FK_Employee FOREIGN KEY (EmployeeID, EmployeeDivisionID) 
        REFERENCES dbo.Employee(EmployeeID, DivisionID)

   , CONSTRAINT CK_EmployeeProjectSameDivision CHECK (ProjectDivisionID = EmployeeDivisionID)
);

This is what's going on here:

We have two composite foreign keys here, one for Employee and one for Project, so we can have the DivisionID for both entities in the same table and guaranteed by DRI to be correct.
These composite foreign keys require a UNIQUE index on the referenced tables. So in addition to your primary keys on EmployeeID and ProjectID in those tables, you'll need unique keys on (EmployeeID, DivisionID) and (ProjectID, DivisionID).
The CHECK constraint guarantees that an employee can only be assigned to a project owned by the same division.

If you want to see another example of this design pattern, here's the answer I gave to a design problem that had similar requirements.

ER Modeling; multiplicity basics – one to many or many to many

I think your design could be stated more formally as:

One company has many projects. 
One project belongs to one company.
One project has one or more activities.
One activity has exactly one activity type (prototyping, design).
One activity belongs to exactly one project.

So, if that's true, you have the following tables:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

ACTIVITY_TYPE
ActivityTypeID
Name (e.g. "Prototyping", "Design")

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...

Best Answer

Related Solutions

Constraint many to many table between two child tables

ER Modeling; multiplicity basics – one to many or many to many

Related Question