I think that I'm stuck...

[cheapseats]

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

[Fenton]

These kind of many-to-many connections can only be done with join tables.

Edited November 13, 2007 by Guest

[Fenton]

Also, you need to use relationships instead of "field_something1, field_something2, field_something3". Create another child table for this.

[cheapseats]

Thanks for the input Fenton. I guess that this is going to be a little more involved than I originally hoped. A learning experience to say the least.

[eos]

Hi cheapseats,

find attached something I slapped together rather quickly, but which should suffice to illustrate the relational concept of "to each its own", so to say... ;-) All sorts of dynamic mechanisms (optimally, scripts) for populating the child tables, error checking (double entries), removing entries etc. still have to be done.

One thing I would recommend is to not have separate client, investigator and/or target tables. They're all people (I guess !?;-), so they share the same attributes, which makes this 1 (person-)table.

Now whenever you open a new case, just assign people from the person table to a join table (__J__CasesPeople) to link them with the case (happens automatically when the proper portal is used, or can be done better by scripting) and - IMPORTANT - give them one of the available roles (client, investigator, target). This gives you the most flexibility, because a) you can assign any number of people in any role to a case, and one case's investigator may be the next case's target. I assume this doesn't happen (too often), but you get my drift.

I didn't flesh out the nitty gritty details of this, because this is where the real work is, but it should give you a starting point.

Also give roles their own table and just assign the roleID to the the child record in join_casesPeople.

Same goes for documents and notes. Give them their own tables and set up join tables in which you can link them to other objects (cases, people, whatever).

In the cases main layout, you just set up different portals to show the person(s) in each category, along with associated notes and documents.

Now you have a relational and quite flexible structure, which lets you link any number of persons, notes and documents to a case (or to persons).

Have a look into "Manage Database", note the use of primary and foreign keys and have a look at the Relationship Diagram – you'll see, in no time you'll be quite good at relationships ;-)

Contact me if you need more info.

Cheers

Oliver

EyesOnly.fp7.zip