AnsweredAssumed Answered

Need help designing database for complicated relationships

Question asked by iimpact on May 31, 2016
Latest reply on Jun 2, 2016 by erolst

Hello,

 

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:

Screen Shot 2016-05-31 at 11.15.35 PM.png

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.

 

Screen Shot 2016-05-31 at 11.10.18 PM.png

Outcomes