Forum Discussion

StephanieHarris's avatar
StephanieHarris
Qrew Assistant Captain
7 years ago

Possible Need for Many to Many Relationship

I currently have 3 tables: Investigations, Subjects and Observations.
  • 1 Investigation can have many Subjects
  • 1 Subject can have many Observations 

There are 2 types of Observations
  • Specific - We know who the subject is and are going to observe only them
  • General - We are observing an area and this observation may identify 0, 1 or Many Subjects

Today:
  • If an investigation has 2 Subjects that were both identified as a result of the same Observation we just pick 1 Subject and related the Observation to that person
  • I can tell you how many observations were done in a year
  • I can't tell you how many subjects were tied to those observations.


I want to be able to:
  • Run a report that shows how many observations were done
  • Run a report that shows how many subjects were related to those observations
  • Run a report that shows the action taken on each Subject that was related to an Observation (action is MC Text field on the Subject Tables)

Not sure if I need to create a new table for General Observations and create a relationship that 1 Observation can have many Subjects & Keep the existing Observations table only for Specific Observations where 1 Subject could have many Observations
OR
Create some sort of Many to Many relationship with a Join table of some sort
OR
Something else entirely

Relationships
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    This is a fun one.  We have goods guys and bad guys.  I assume that you are on the "good guys" side goin' after the bad guys.

    Yes, you do need a middle table.  I think you need it like this


    One Investigation has Many Observations. (It seems to me that you would want this relationship)
    One Observation has Many Subjects Observed (this is one half of the join table)
    One Subject has many Subjects Observed (this is the other half of the join table)

    hence, you will need that new join table for Subjects Observed

    In terms of your needs.

    • Run a report that shows how many observations were done. So that sounds easy.

    • Run a report that shows the action taken on each Subject that was related to an Observation (action is MC Text field on the Subject Tables). I think that you would do this MC field on the Subjects Observed join table

    • Run a report that shows how many subjects were related to those observations. So you can easily tell how many Subjects Observed records there were for an Investigation, but the issue is if its the same bad dude Observed many times in many Subjects Observed, I'm guessing that you only want to count him once.  You could do an embedded summary report on the Investigations record of the Subjects Observed, and it would show you a list of the unique bad dude Subjects. 






    • StephanieHarris's avatar
      StephanieHarris
      Qrew Assistant Captain
      7 years ago

      You're correct, I am one of the "good guys" :)  We originally made the relationship between the Subject and the Observations so that we could show exactly which Observation was connected to which Subject.  Is there a way to keep that relationship with the new Join table and still have everything I need?
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      OK, good to know that you are one of the good guys even though I you are a gal and not a guy.

      I think that you need to delete the relationship where 1 Subject has many Observations because a child can only have 1 Parent.  So that would mean that for a given Observation, you could only have 1 Subject, but if this is two bad guys doing a drug deal in a back alley and both being Observed by the Good Guy, then that is One Observation and yet there were 2 Subjects Observed.

      So that is why i thing that you need

      One Subject has Many Subject Observations.
      One Observation has Many Subject Observations.

      If you have existing data in your app, it can be migrated to the new structure with some table to table copying and maybe some one time excel work.