I was in a similar situation along the lines of clients, authors, employees, etc....
My need to have lists (ie portals) of ALL of these people decided the issue for me: one table for all persons. Furthermore many persons have multiple roles (some clients are employees for instance). It was important that info for these people not get duplicated and split among multiple tables.
On the other hand, I wanted everyone to have a 'contact' page, but only 'employees' to have an employee page.
I read as much as I could find on the issue, but the solutions I found were either inadequate, or overly complex (one person outlined their method of having duplicate 'shadow' tables that aggregated the different 'person' tables to generate the portals/lists of all persons. Clever, but too complex and script driven for my taste.
My solution (which I have thus far been happy with) is one table with many layouts. This way if a person was a client, author, and employee- I could show them as all three without the hazards of duplicate data. Each layout is simply a different point of view of the same record.
The tricky thing for me, especially since it was unacceptable/alarming for my clients to ever see a contact as an 'author', was to take control of finds and navigation. Tricky, but I was happy to do it as I have never liked filemaker's navigation bar. I created an interface for finds and navigation such that if you are in the 'employees' layout, moving about and performing finds takes place within the subset of employees. The final result took some doing, but it actually only involves a few simple scripts and layout objects.
Hope that gives you some ideas.
May I suggest one list with a tag field with the selection of the type of person being by radio button with no other choice, that way that person can ONLY be one type with a verification to ensure that? A radio button selector should also ensure the elimination of typo's and unrecognisable tags.
Thanks for the responses.
I've been thinking that it will have to be one table for all the people, but probably separate tables for relationships (since there are some many->many relationships, like siblings, cousins, etc.), and probably separate tables for roles, now that I think of it, because a Witness has a relationship to a Case which can also end up being many->many. In fact, I now realize that Witness doesn't actually make much sense, except as a person is related to a case. And, again, since it's many->many, that would seem to require a join table.
I'm surprised there don't seem to be any examples of stuff even close to this on the Internets!
In any event, after sleeping on it, I realize it's going to be more complicated, but possibly in some ways also more simple, than I first thought. I'm going to have to have a Person (or People?) table which will have limited information in it, like the primary key, a true first name, true last name, possibly a true middle name, maybe an SSN, DOB, and not much more. That table can only hold the stuff of which a person just has one. But names, for example, in my situation: people have aliases, and nicknames. They have sometimes more than one address and phone number. And so on.
I sure hope I haven't bitten off more than I can chew!
Robert P: As far as the layout, thanks. The real question, though, is what tables and relationships are needed. I agree with you on the layout, and already decided that there will be drop-downs for relationships like brother, mother, father, sister, cousin, grandmother, etc., etc. And probably also for the types of witnesses.
Thanks for the responses, folks! I hope this discussion goes on; it will be interesting to see other ideas!
A "many to many self join" relationship can be used to chart relationships between people:
People::__pkPeopleID = Relationship::_fkPeopleID1
People|Related::__pkPeopleID = Relationship::_fkPeopleID2
People and People|related would be two Tutorial: What are Table Occurrences?.
a field in Relationship would document the relationship.
Say you have two records in people:
George Smith , __pkPeopleID = 1
Anne Smith, __pkPeopleID = 2
You could show George as the father of Anne by creating a relationship record where:
_fkPeopleID1 = 1, _fkPeopleID2 = 2 and a text field in Relationship records the value "Father".
You could then show Anne as the daughter of George by creating another record in Relationship where:
_fkPeopleID1 = 2, _fkPeopleID2 = 1 and the same text field records "Daughter".
One would frequently manage such relationships by putting a portal to Relationship on a People layout and format _fkPeopleID2 with a drop down list of ID's and names from People. A script might be devised to create the second relationship record to automatically create the second link from the info provided for the first link.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
If you have never set up a "use values from field" value list--the type of value list typically used with a field such as _fkPeopleID2, There's a very detailed description of how to set up such a value list in: "Adventures in FileMaking #1 - Conditional Value Lists". This file's focus is on conditional value lists--something that you do not need for this initial problem, but the first exploration layout in this file walks you through the process of setting up a Use values from field value list step by step.
Oh, thank you soooo much!
I started trying to map everything out in LucidChart, and realized that it really needed to be a many-to-many self-join, but I was just trying to figure out how I'd do that, when I saw your response.
Now to puzzle through figuring it out. (I haven't done database programming in over 25 years.)