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...
Forum Discussion
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.
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.
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.
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.