I am a relative noob with FileMaker so please excuse me if this seems to be a stupid (or redundant) question.
As a learning tool, I offered to build a database for a friend of mine that is a Private Investigator. I have tables of "notes", "persons"(targets), "containers", "clients", "investigators" and "cases."
When a new case is taken, the client information is entered into the database in the "clients" table. If there is an insurance company or an attorney associated with the case, that information is also entered into the "clients" table. The person(s) being investigated is entered into the "persons" table. Then, the case is entered into the "cases" table where the client and person being investigated are joined.
As the investigation progresses, the investigator will enter additional information into the persons table for the person(s) being investigated. If the investigator has notes to post to the case, they would do so in the notes table. Any addtional documentation (pdf file, word doc, excel spreadsheet, digital recording, pictures, etc) would be posted to the container table.
The person(s)(target), notes and documents would be tied to the case table through the use of portals.
Here's my dilemma...in my study, it appears that having a many-to-many relationship is pretty much comparable to committing one of the seven deadly sins. Now, it is conceivable that one case could have more than one client and that one client have have more than one case, especially divorce attorneys and insurance companies. Also, one case can have as many as ten people being investigated and each person in the "targets" table could be involved in more than one case.
Am I in trouble here? Am I just being paranoid about these parent-child relationships? Would you advise a totally different structure to this database?
Your advise would be greatly appreciated. I have attached a rough layout of the database.
cases.fp7.zip
