One person multiple names

[ilcaffedio]

I need to implement a solution to track multiple people per event. Linked to the event on a many-to-many relationship based on their role in the event.

Where this gets tricky is that the people are known only by their names, and each person can have multiple names (formerly known as, etc...).

Also for each persons full name there can be more than just First, Middle, and Family names. For example "Susan Ann Smith Johnson". Whereas she has two family names, and it can be assumed that she was formerly known as "Susan Ann Smith", therefore a relation to "Susan Ann Smith" should be linked to the person, now known as "Susan Ann Smith Johnson" based on formerly known as.

So I need multiple names per full name, based on type, such as First, Middle, Family with proper ordering. And multiple full names per person, whereas the only identifier for each person is ther set of names and spouse.

Implementing this with scripts and an interface to edit multiple full names per person, whereas each full name has multiple name parts is driving me nuts. Any suggestions? How would you implement this?

[RalphL]

If you have to do the multi-name thing, then you need a related table of names. Your people table would have the ID number and would be used in the many to many relationship with event.

It would have a one to many relationship with name.

In your example would Smith Johnson be the family name?

[ilcaffedio]

Well I know that I need to a related table of names either through filemaker relationships, or with a join table that is added to with a script, as in business tracker. As far as I know the only, or best way to create joins more than one table away is through scripts which handle the whole process. I need for each person to have a many to many relationship with each full name. (each person can have more than one name, and more than one person can have that name). Also there needs to be a many to many relationship relating each name (susan, johnson, smith) back to the full name. My problem with this is writing the scripts to handle all of this. Also being able to create multi-keys, for the person, and full name.

[Ender]

Hmm, I wish I knew what the purpose of keeping a history of the names is--do you work for the government or something?! Usually one name is stored for each person and a change to the name of a person automatically is updated throughout the system because they are referenced through a relationship to the Person table. This gets back to what Ralph was sayng; your tables should be linked by ID numbers and not by names.

In any case, I don't see any need for a many-to-many between Person and Name. Even if you are tracking multiple names for a person, that's one person to many names. If a particular name happens to be same for more than one person, it's not significant to the relational structure (unless these are spy code names or something, where they get assigned to different people; one day "007" is Roger Moore, the next he's Sean Connery.)

[ilcaffedio]

Actually I work in law. So these are names of people pertaining to legal documents. One person may go by one name on one document, and another on the next.

Also, Bob Brown SR, and Bob Brown JR, may both go by Bob Brown. The only way to tell them apart is that Bob Brown SR has a spouse named Susan Brown, and Bob Brown JR has a spouse named Elizabeth Brown. So on a document for an event with Bob Brown, and Elizabeth Brown I can assume that it is Bob Brown JR. However I need to be able to relate "Bob Brown" verbatim to that document, but "Bob Brown" is linked to a person who also has the name of "Bob Brown, JR".

This is a very simple case where the names only consist of First name, Family name and Suffix. Though I need to allow for longer names with multiple parts.

[comment]

I think you might have missed the significance of Ralph's advice:

Base ALL your relationships on auto-generated serial numbers.

After you have assigned a person to an event (by joining PersonID to EventID in a join table), pick the name by which this person is to be known in this event, and add that name's NameID to the joining record.

[ilcaffedio]

Thank you. I think that makes sense for each person having multiple full names. How do I solve each full name having multiple name parts?

[comment]

Uhm... why would that be a problem that needs solving?

You haven't told as much about the purpose, but I guess each Name record should have - in addition to NameID, of course - a FirstName, MiddleName, LastName, Title, Suffix, Alias and whatever-else-you-might-think-of fields.

Then make a calc field cFullName to display the concatenated version. Or several, a short one, a long one... whatever you need.

--- EDIT ---

If I were tracking changes in people's names, I would also add the date on which this name took effect (with appropriate notes on why, how where, etc.). This would enable me to know what the person's name was on any given date.

Edited August 27, 2005 by Guest