Possible Need for Many to Many Relationship

Question

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

QuickBaseCoachD · Answer

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.

Sign up

Login to the community