In need of help: A PARTY can have many PARTY NAMEs…

[dmontano]

SITUATION:

A PARTY can have many PARTY NAMEs. Each PARTY NAMEs must be classified by a PARTY NAME PURPOSE TYPE such as “Legal Full Name”, “Common Name”, “Preferred Full Name”, “Nickname”, or “System of Record Name”. I wish to record the many PARTY NAMEs a PARTY can be referred by.

EXAMPLE:

• *John James Doe “Legal Given and Family/Surname Name”;
• *John James Doe “System of Record Name”;
• John Doe “Preferred Full Name”;
• Johnny “Preferred Given Name”;
• Big John “Nickname”.

* Notice these two names above “John James Doe” are identical, but their PARTY NAME PURPOSE TYPE is different. This is acceptable and important: It is good to know that it is the “Legal Given and Family/Surname Name” of John James Does is being used also as the “System or Record Name”.

CURRENT SOLUTION:

Three tables:

• PARTY
• PARTY NAME
• PARTY NAME PURPOSE TYPE.

Relationships:

• PARTY is related to PARTY NAME in a one-to-many relationship: one PARTY can have many PARTY NAMEs.
• PARTY NAME PURPOSE TYPE is related to PARTY NAME in a one-to-many relationship: one PARTY NAME PURPOSE TYPE can be used to classify many PARTY NAMEs.

Layout:

• Based on PARTY, contains a portal to PARTY NAMEs. The portal row includes a field to store a PARTY NAME and a field to select which PARTY NAME PURPOSE TYPE a particular PARTY NAME is to be classified as.

This solution appears to work just fine, and I believe is quite straight-forward. However, I have a problem I can not solve.

PROBLEM:

The PARTY table requires one PARTY NAME value in order to properly refer to the PARTY record and to be utilized in other relationships within the database. If the PARTY NAMEs table is storing all possible names for a PARTY, how can I get one of the PARTY NAMEs into a the name field in the PARTY table? The field in the PARTY table that would contain this name would be titled “System of Record Name”, which is one of the values in the PARTY NAME PURPOSE TYPE table.

It seems I am looking to go in the opposite direction of a typical one-to-many relationship to utilize one-of-many possible values to be a specially designated value in the “parent” table. I am trying to place a “child” value into a field in a “parent” table in order to instantiate the parent.

CLOSING:

I hope this and the sample file attached makes sense: I am having a hard time describing this.

Thanks in advanced for your help, David

party_name_dfa_220710a.fmp12.zip

[bcooney]

When you create a party record, require the system of record name, and cache it in the party record. 
 

id have user enter all info for party in globals and send a json object to an api transaction that would create both the party record and the first party name record. 
 

not doing person and organization subtypes?

[dmontano]

Hello bcooney, long time since I've been back here and always appreciate your help!

Yes, I have those subtypes but stripped out to avoid confusion in sample file and avoid possible debate about its value/merit.

Back to your response: your answer involves Json/API. Consider these terms over my head, but not entirely. Allow me to avoid that approach for now, given complexity to me. Is it safe to say in simplistic terms:

During data entry, hold name value as a global (like being suspended) and then "set" in desired field in Party Name, then subsequently set "that" Party Name key in Party Table?

I know the importance of scripts, however I was hoping to accomplish without using scripts and have considered my issue potentially being a model problem.

Thank you for your time/contribution. I'm still interested in any other thoughts.

[comment]

5 hours ago, dmontano said:

The PARTY table requires one PARTY NAME value in order to properly refer to the PARTY record and to be utilized in other relationships within the database.

Uhm, no and double no. The most a party record is allowed to store is the foreign key to the name selected as the "“system of record" name. Or - provided that each party is allowed at most one name of each type - the foreign key to the type of the selected name.

Names can (and often do) change. Storing the same name twice would require executing each name change twice - and expose you to update anomaly in case it failed. And - for the same reason - you most certainly do NOT want any names "to be utilized in other relationships within the database". Any such relationship would break if the name were changed - even if only to correct the spelling.

If you want, you could take the  "“system of record" name out of the Party Names table and store it only in the Party table. But then you wouldn't be able to easily list all names of a party.

 

[bcooney]

Whoa, didn’t catch that. Hopefully, you’re not using a name in a relationship! I debated whether it is helpful to store the PartyNameId in Party. Maybe.  I still would cache the value, comment. Any updates to party run thru transaction process. 

[comment]

1 hour ago, bcooney said:

 I still would cache the value, comment.

I don't know what you mean by that. It seems to me that this question is about structure and you are taking it to procedure.

Edited July 11, 2022 by comment

[dmontano]

Hi Comment, so nice to hear from you once again: it's been a long time since I have been here, and a long time since I "cracked-open" Filemaker. I just wanted to say thank you for the reply.

First, I assure you I understand the importance of using keys for relationships and I am sure my language use is cause for the speculation (and fact) that I may not be using keys. I used only "party_name" in my PARTY table, but I thought I had good reason as stated below.

In my sample file I attached originally, on PARTY layout I have a orange area with a "drop-down-to-a-related-party-value-list" of "party-names" that exist for a particular party: it is this temporary list that did NOT use keys and may have given the impression that I am in the practice of not using keys. That is my bad for throwing the idea together hastily.

I have taken what you and bcooney have provided input on and have done my best to understand. I suspect I am failing to understand in some areas. In that spirit, I want to recap my understanding of what was communicated:

1.  A PARTY NAME foreign key should exist in the PARTY table to record the ONE particular PARTY NAME I wish to use as a "System of Record" name.  This makes perfect sense. I believe I "used" the "name" field instead of the "key" field because the relationship from PARTY to PARTY NAME already established the "integrity" of the relationship - if given that no PARTY would have two of the exact same names and I was pulling the name values from a value list that restricted only names related to that PARTY. As you pointed out, I could have and should have used keys anyways: this has been corrected.

Upon further investigation, there could be two instances of the same name for a single PARTY in the PARTY NAME table. This occurs when the "same name" are assigned different PARTY NAME PURPOSE TYPEs.

• John Doe : Legal Name
• John Doe : System of Record Name

I understand this now. Perhaps tables should have been:

PARTY

• ID 1
• ID 2
• ID 3

PARTY NAME

• Johnny Doe
• John Doe
• Bill Smith

PARTY NAME PURPOSE TYPE (Many-to-Many)

• Johnny Doe, Preferred Name
• John Doe, Legal Name
• Bill Smith, Legal Name

NAME PURPOSE TYPE

• Legal Name
• Preferred Name

2. I originally had a "party_name" field in PARTY; AND a one-to-many relationship PARTY to PARTY NAMEs to store all other names of a PARTY. I did not (and still do not) like that approach because names would be stored in two different tables. This is more a gut feeling than a hard use-case example I have to validate this.

Off hand, I imagine any use of these names down the line where I need to use them for communication purposes (mail-merge type activities) would be problematic: wanting to use "System of Record", and other cases wanted to use "Preferred Name" or situations where "Preferred Name" does not exist and need to programmatically fall-back to "System of Record" name.

ALTERATION TO SAMPLE FILE

I have revised my file (attached here) with what appears to be working. This approach stores all PARTY NAMEs in the PARTY NAME table. I like the fact of storing all names for parties in one table. It NOW includes a foreign key to the PARTY NAME table (as you suggested) and appears to provide what I seek: all PARTY NAMEs stored in PARTY NAMES; and the ability to enter a PARTY NAME on the PARTY layout, but stored in PARTY NAME. I also have the ability to record additional party names, and I have the option to select from possible PARTY NAMEs related only to a particular PARTY to become the "System of Record" name.

I messed around with the "party name selector" idea (shown in orange) that I first presented but not functional at the time. I think it is working now and "honoring" appropriate database "rules". I do not quite understand what and how it is doing what its doing, but hopefully time will allow me to understand.

My sample file is rough and has some issues: for example, I believe there should only be ONE "System of Record" name for each PARTY. I also believe that the default value for records imported (given they are new parties and only one name field provided) should all default to being the "System of Record" name.

If imported records contain some parties where their name is simply their given name and not their family name, they would still need to be recorded as "System of Record" names. Once new "name" information about a PARTYs is discovered, those names can be added AND if a new name added to a PARTY is much better suited to being the "System of Record" that name can be assigned as such.

Sample file attached.

Any input to clarity, improvements, or problems you see - would be greatly appreciated.

party_name_dfa_220711a.fmp12.zip

[comment]

I hesitate how to answer because I do not know what your business rules are and I don't really understand what is the purpose of all this.  I have a feeling that a "System of Record Name" is not a type of name like the other types. Rather it is just one of the many names that a party has that was chosen to be THE name. IOW,  in your arrangement there will be always a name that is identical to the  "System of Record Name", but has a different type. If I am correct on this, then you should remove "System of Record Name" from the types table.

Regarding your structure, I believe you need at most 3 tables:

Parties -< PartyNames >- NameTypes

As mentioned, you will need an additional relationship between Parties and PartyNames in order to designate the name selected as the "System of Record" name. But you do not need an additional table. In fact, you probably do not even need the  NameTypes table, and could simply use a value list instead.

Note that in this arrangement all names are stored in the PartyNames table, and in the PartyNames table only. There should be no name field in the Parties table (except possibly an unstored calculation field to display the "System of Record" name from the PartyNames table).

 

[dmontano]

Hi Comment, I'm stepping out on a limb here to respond to business rules and the purpose:

1. Every Party must have at least 1 name. (Business Rule)

2. In the case of a 1 name party (which would be the majority of cases) that Party Name should be identified as the "System of Record Name". (Business Rule)

By declaring the System of Record name, it allows all personnel to refer to a name within a database system to answer questions such as: what marketing communications have we sent to "A.Smith".

3. During the course of business, we may learn more "names" of a particular party and wish to store these additional names. At first, we may import a "name" such as "A. Smith" as this is the only known name at the time for this Party. The business rule would be that if we only have 1 name, it should be considered and recorded as the "System of Record Name". This System of Record Name is the name that we will use in the database to identify this party. While a PARTY name will never uniquely identify a PARTY due to duplicative names in the real-world (which we could "concatenate" a phone number or email for display validation only purposes to confirm we will be selecting the correct Party when a transactional record like an invoice is created, etc).

4. Later we discover that the Party associated with the PARTY NAME of "A. Smith" has additional names that we can associate the Party with: a Given Name of "Alexander"; a Preferred Name of "Alex", a middle name of "William".  We may wish to create the following Party Names:

• A. Smith (Imported Name)
• Alexander William Smith (Full Legal Name)
• Alexander (Legal Given Name)
• William (Legal Given/Middle Name)
• Alex (Preferred Given Name)

Given the above example, we would indicate that "A.Smith" was the "imported name". "Imported Name" allows us to accurately understand that this name is how we imported the name and we cannot and should not infer we know the structure of the name. "A." could be a Given first name or a Given middle name, etc.

A. Smith (being the only name we have) should ALSO be defined as the "System of Record Name", separately from its other attribute of "Imported Name".

During the course of business and interaction we learn more about "A.Smith" as shown above in terms of his name. Those PARTY NAMEs should be recorded to allow a salesperson to know the Party's "preferred name"; that our legal department will know the "legal" name for contract purposes, etc.

3 hours ago, comment said:

I have a feeling that a "System of Record Name" is not a type of name like the other types. Rather it is just one of the many names that a party has that was chosen to be THE name.

Agree. I can now see that the combination of a PARTY NAME [Alexander William Smith] and its PARTY NAME PURPOSE TYPE [Full Legal Name] can then be optionally selected as the "System of Record Name". I have three attributes here in this example, not two.

4 hours ago, comment said:

IOW,  in your arrangement there will be always a name that is identical to the  "System of Record Name", but has a different type. If I am correct on this, then you should remove "System of Record Name" from the types table.

Agree. This is now crystal clear.

4 hours ago, comment said:

As mentioned, you will need an additional relationship between Parties and PartyNames in order to designate the name selected as the "System of Record" name.

I am failing to imagine a relationship that would exist between these two tables that would provide the "System of Record" designation. I'm not doubting you, I'm not able to imagine how. The only thing I can think of is a field in the PARTY table, such as "party_name__system_of_record_name_fk" that related to the PARTY NAME table. Is this what you mean?

[comment]

1 hour ago, dmontano said:

The only thing I can think of is a field in the PARTY table, such as "party_name__system_of_record_name_fk" that related to the PARTY NAME table.

Yes, that's exactly what I described in my first answer above. See the attached demo file:

SelectedChild.fmp12

[dmontano]

Thank you comment, it must be frustrating when you help those and they don't understand something you posted earlier in the thread! I really did try and go over your comments and think. I am certainly making progress on this topic, but not to the finish line yet.

I appreciate the sample file you took the time to create - thank you.

I am off to study and attempt to get this wrapped up.