I have slowly started building my database but have come across a needed relationship that I am not sure I know how to handle.
Currently, the database is designed as follows:
- Method layout
- Add new methods to Method table
- Sample layout
- Add new Samples to Sample table
- Associate 1..n Methods to Sample
- Report layout
- Add new Report to Report table
- Need to associate Report to selected "Sample and Method" combination
I am currently having trouble associating a Report to a Selected number of Samples (based on Method). See diagram below:
What I am looking to do is in the Report layout, be able to generate a new Report for a given Method (let's say Method 1), and then have that Report associated with n Samples (Sample 1, 2, and 3). A Method <--> Report relationship is not unique, as I need to be able to assign a different Report to Method 1 with associated Samples 4, 5, 6.
Here is my current DB schema. I have assigning multiple Methods to a single Sample working fine, but am having trouble trying to layout the desired relationship described above.