I have no issue with the name field (or fields) being in the supertype table. In fact, my own little address book (made in version 4, before I knew anything about relational databases) already has a single LastName/CompanyName field.

However, had I decided that FullName must NOT be calculated from the other name components, I would have to enter every single name component at least twice (with no doubt some typing errors creeping in during the process). That seems to be a high price to pay to accommodate a few exceptions.

However, had I decided that FullName must NOT be calculated from the other name components, I would have to enter every single name component at least twice (with no doubt some typing errors creeping in during the process). That seems to be a high price to pay to accommodate a few exceptions.

I didn't say that you shouldn't have some mechanism by which to facilitate data entry, but that is a tangential issue compared to the modeling of the data.

I go back to the idea of a "PreferredName." In the data model it is a separate attribute from "FirstName" or "GivenName" or what have you. At the same time the assumption is that unless specifically stated otherwise we can assume the "FirstName" is also the "PreferredName" and can use data entry techniques (auto-enter calculation, scripted process, script trigger, etc.) to facilitate this.

It's a similar concept to that of web checkout forms that allow you to say your shipping address is also your credit card billing address. They present a simple mechanism to help you avoid double-entry of data but that's not necessarily indicative of how the data is stored on the back end.

FMP makes it difficult to separate the collection and presentation of data from the underlying storage of data. At the same time, however, I don't believe that additional effort is significantly more than the effort required in other systems. On the contrary, I think what FMP excels at is making it incredible easy to create both simple and complex database applications with its tight data/interface binding. Unfortunately for most systems in the case of the latter it often comes at the expense of being poorly modeled and therefore susceptible to problems down the road.

I am not quite sure what you are suggesting, in practical terms. Certainly, the labels "FirstName", "GivenName", etc. are of no importance - what's important is that the correct "name element" is readily available when and where needed.

To take the simpler example: what exactly is the advantage of storing duplicate shipping and billing addresses as a default, compared to using an override rule for the billing address? It seems to me more an issue of workflow and user interface than data modeling.

Thanks for the further contributions Corn, Comment, and DJ.

I am actively working on this "type" of structure in a FileMaker solution - so this is all very helpful.

Hi Corn, yes I am from the US and your point is well taken about names. Could this be solved by modeling names as:

TABLE: person_name_types

ASSOCIATIVE TABLE: person_names

TABLE: person

John (first name type)

Robert (middle name type)

Allen (middle name type)

Doe (last name type)

III (suffix name type)

But once again these items would have to be concatenated to have meaningful use when selecting a "party". There is obviously a need to have both: single values where appropriate and combined values where deemed appropriate.

I really wrestled with "where" to put the "name" fields... in the super or the subtype. As a matter of fact I went back and forth a couple of times. Currently, the name fields reside in each subtype table. I believed I moved them back in their respective subtype tables because of this very issue of the names for people are broken out and identified by their individual components.

The problem this has left me is "unstored calc" fields for names and I strongly suspect this is not a "good" thing.

So how does one go about creating a name field in the supertype that can serve the other needs of just providing the first name, or the last name, etc. I am not clear on what is being suggested.

It seems to me that the whole "names" thing is getting a bit esoteric. And back-to-front. Determine the data that is needed, then store it appropriately.

If there is a need to store the person's "full name" to print on an envelope, than store it: "Professor Jon Robert Allen Dow III".

If there is a need to store a salutation for a letter, then store it: "Prof Dow" or "Robert" for formal and informal.

Yes this loads up the data entry hugely, and I have never done it this way and have no plans to because I've never seen it encountered. This academic discussion about name storage is getting a bit frustrating, because it appears to be a solution looking for a problem.

Sorry for the rant. :)

If you're truly sorry for ranting then don't rant and then you won't have to be sorry.

The names issue is precisely the problem here: of what use is the Party supertype if it contains no attributes. More specific to FMP, how do you display anything meaningful in a portal of Party records if all of the attributes are located in the subtype tables and the portal contains a mix of subtypes?

The obvious answer is to store an attribute - you can call it "Foo" but I'm going to call it "Name" - in the Party supertype table. This name will identify a particular party, whether it be a company, a person, or perhaps an aggregation of people.

Here's where the "cultural construct" comes into play. Because of a Western bias we tend to think of names as "first middle last" and that there is this thing called a "full name" that can be constructed from "first" + "middle" + "last". It's a lie I tell you. Let's take a real person, 'Dr. Phil'. His full legal name, should I need to store it, is 'Phillip Calvin McGraw'. Now there is some question here - do we store that as 'Dr. Phillip Calvin McGraw, PhD' or do we keep the honorific and titular attributes separate? Meanwhile he is more commonly known as 'Dr. Phil' in most situations - should we just store that as the "full name?" All these names and what to do...

But that's exactly my point. If you only have a view of names as being "first middle last" then storage of the so-called "full name" becomes a problem because you tend to see it as a derivation of the separate attributes. Once you see that "first middle last" is just one of many different naming conventions and that they are insufficient for composing many of the names you will need to actually use in your system you will find, I believe, that storing a "Name" attribute in the PARTY table is the least of your worries.

Okay, I've been reading along and see your points (and I am speaking to all of you). But if you split what you know should be split and store how you know it should be stored, it is far easier to grab what you need when you need it than to later attempt to pick it apart. As I was told once, "why put it together only to pull it apart later?"

I suggest providing every possible piece in a separate field. If a User isn't sure how portions should be placed then they can 1) ask the customer while speaking with them (WHAT A CONCEPT) or 2) guess. Even a guess is better than dumping everything into one field simply because it seems like too much work to do it right the first time.

Well, that's my opinion anyway ... :)

Edited November 6, 200817 yr by Guest

Corn & LaRetta

I think we're all on the same page.

Now that we have names sorted out, shall we move the discussion on to handling addresses as well? :hair:

My ONE post in this 4-page thread means I'm over-talking a subject? I thought expressing our opinion on databases is why we are here! :wink2:

Edited November 6, 200817 yr by Guest
Reworded a bit

I personally like the "provide all possibilities" approach. If I DO NOT provide a field label that prompts a data entry person to enter the "last name" then the field will end up with some first names, middle names, nicknames, and whatever else name is known at time of entry. To a large degree, these "broken" out fields for names and their respective labels do help promotes some data consistency on data entry.

I know the value of the above observation is not what is being argued. Furthermore, the "Name" as one complete attribute makes sense, and the "first" name is really only a fragment of the whole. However, we do refer and infer in fragments. By separating them to begin with, I believe we have a much higher probability of success of reconstructing the name as we see fit for a particular application - versus breaking a whole name down into useable fragments. For example; formal name when we are sending legal notice; informal when we are inviting the person to our company weekend bbq.

But in the context of supertype and subtype, I think the primary issue is "where" to store the names. Corn, you made it clear that they should reside at the supertype level. Makes sense and follows all that I have read about common attributes in super, unique attributes in subtypes. (For the life of me I can not recall why I went back and forth and finally settled on placing them in their subtypes.)

I have posted a slice of a screenshot that shows my field definitions in my "party" supertype table. I still have left the "name" fields in their respective subtype tables and you will notice that I have unstored calcs for the names in the parties table (these values are coming from related tables). I suspect that the unstored calc for the "names" is bad. Can anyone confirm this.

If this is bad, then I have to move these name fields AGAIN back into the supertype - right?

If this is the case, is this an example of FileMaker being difficult to work with or weak in trying to implement a normalized schema?

I think what you're getting hung up on is the idea that either the Full Name should be derived from its constituent parts (e.g. First Name, Middle Name, LastName) or that the individual name fields should be derived from the Full Name.

What I am advocating is that the two are stored separately - "Full Name" in PARTY and FirstName, MiddleName, and LastName in PERSON. Likewise in ORGANIZATION you might have attributes specific to organizations (like it's legal name) but it's "Full Name" is also stored in PARTY. I hesitate to use the term "Full Name" here since it is a fuzzy term (and in my models call the attribute "Name" only) but I think you know what I mean.

That is NOT to say that your data entry forms and methods should force the user to enter data twice. On the contrary, you should make data entry as convenient as possible while encouraging quality data. So if you want to capture FML as separate fields by all means do so, and automatically calculate the "Full" name to be stored in the PARTY table as a convenience. But... you should provide a mechanism by which they can change the Name in the PARTY table to something meaningful. So if they have two "Joe Wurzelbacher" records they can modify the name to better distinguish them. One might be 'Samuel "Joe the Plumber" Wurzelbacher' while the other is 'Joe Wurzelbacher Sr.'.

Which is also to say, you should have fields specific to the purpose for which they're needed. For example, if you need "addressee" or "formal salutation" fields you should create and label them as such. When someone asks for "First Middle Initial Last" I ask them how they intend to use it. More often than not the intent is as an addressee field so that's what I give them. I have other fields that are used literally for filing - for example a "File As" field that might contain 'WURZELBACHER, Samuel Joseph (Joe)'.

When storing the PARTY name fields, instead of using unstored calc fields, I use text fields with an auto-enter. In the PARTY table I also have a "name trigger" field that gets tickled to trigger the various auto-updates and set that field by script when editing PERSON or COMPANY fields.

Is this a sensible thing? I do it so I can view, find, and sort on a stored field rather than unstored.

When storing the PARTY name fields, instead of using unstored calc fields, I use text fields with an auto-enter. In the PARTY table I also have a "name trigger" field that gets tickled to trigger the various auto-updates and set that field by script when editing PERSON or COMPANY fields.

Is this a sensible thing? I do it so I can view, find, and sort on a stored field rather than unstored.

Sorts don't use the index although Finds do. Since I am explicitly saying the "Name" field in PARTY is an "identity" field that means I would have it stored and indexed.

The operative concept here is that this Name field can be modified independently of the various name fields stored in the subtype. If this is not desirable in your situation then an auto-enter calculation can work for you. You might consider using a script triggering plug-in like ZippScript to force a re-calc of the PARTY::Name field when any of the other name fields in PERSON or ORGANIZATION are changed. Alternatively you can use a SQL plug-in to modify the PARTY::Name field in the same way (this is pretty cool, no scripts needed to update related data but if the record is locked, the update doesn't happen).

The operative concept here is that this Name field can be modified independently of the various name fields stored in the subtype. If this is not desirable in your situation then an auto-enter calculation can work for you. You might consider using a script triggering plug-in like ZippScript to force a re-calc of the PARTY::Name field when any of the other name fields in PERSON or ORGANIZATION are changed. Alternatively you can use a SQL plug-in to modify the PARTY::Name field in the same way (this is pretty cool, no scripts needed to update related data but if the record is locked, the update doesn't happen).

This thread isn't quite long enough, so I thought I'd try to resurrect it! ;-)

I always forced the Name field on my users and never left it up to them to modify, mostly because I didn't like options for triggering the update on the Name field. Without automatically updating the field I figured I'd get a lot of garbage entered or not at all (and don't get me started on FileMaker validation). This has changed now in FM 10, because we now have a reliable approach to updating data, even if it exists in related tables.

For instance, you could set an indicator Name_Modified_Ind which is set when you've manually entered a value in the name field, and cleared if you erase the current value. Then using OnObjectModify event traps[1], you could update the Name field from any or all of the name fragment fields in a live fashion (i.e., as you type), but only if Name_Modified_Ind is empty. Providing this type of realtime feedback to the user is immensely valuable, because they no longer have to wait for the record commit or the field exit to know what's going to happen.

_____

[1] I used the same type of technique in a demo file that provides Spotlight-style search filtering.

This has changed now in FM 10, because we now have a reliable approach to updating data, even if it exists in related tables.

I'll take an exception to that. IMHO, nothing has changed. A script is still a script. A transaction that depends on data entry AND a script to be complete is still less reliable than a transaction that depends on data entry alone.

In the context of my post, FM 10 script events are more reliable than a plug-in because: you have fewer failure points, it's integrated with FMP at a lower level than a plug-in, and it doesn't require additional software to be installed.

I fail to see how a user performing data entry alone could be any more reliable than a scripted transaction which controls the entry of data. In my world the opposite is true (i.e., users left to their own devices will fill rapidly fill a database with garbage). Color me puzzled.

In the context of my post, FM 10 script events are more reliable than a plug-in because: you have fewer failure points, it's integrated with FMP at a lower level than a plug-in, and it doesn't require additional software to be installed.

Well, considering that they are "object" triggers they may in fact be a bit less reliable since they are tied to your interface, not to your data. In FMP 10 we still have the problem of the "rogue" interface file that bypasses any script triggering mechanisms.

The disadvantages of the plug-in methods are that they require the plug-in to be installed at the workstation and, in the case of script triggering plug-ins, they run "post-event." The first problem can be addressed rather easily, the second not as well.

I don't know if native script events are more reliable than a plug-in, but that's hardly the issue. From the point-of-view of data integrity, a script is a script - and it doesn't matter if it's triggered by an event or by user clicking on a button. If the transaction isn't complete until the script has run, then there's a vulnerability.

For example, one might be tempted to accompany user entry of a sale by a triggered script to update the inventory - "because we now have a reliable approach to updating data, even if it exists in related tables." My exception is directed at the dangers of such illusion.

Well, considering that they are "object" triggers they may in fact be a bit less reliable since they are tied to your interface, not to your data. In FMP 10 we still have the problem of the "rogue" interface file that bypasses any script triggering mechanisms.

... or even alternate layouts which a developer has overlooked and not added triggering to. I guess what I was trying to understand is why -- other than having an extra potential point of failure -- a scripted operation would be inherently less reliable than just data entry. It sounds as if Comment glossed over the potential issues with users entering data when he took issue with scripted operations. I still submit that a procedure controlled via scripting -- even triggers based on object events -- is still more reliable for the situation that I presented than just data entry alone.

All that said, I would never trust a truly important transaction (e.g., posting to inventory or a general ledger) to a scripted operation initiated by an triggered event. And, I absolutely would require such an important transaction to be performed via script and not data entry.

I think we both agree that a developer should use proper discretion for routines which are triggered by FM 10's new event model. I still don't understand how -- for transactional processes like updating inventory -- data entry could even approach the reliability of a scripted operation initiated by a click of a button.

I have nothing against script-assisted data entry (provided it's atomic). I'm afraid you are arguing against a ghost of your own creation - I never said anything on the subject.

I'm clearly not understanding you then, so I'm going to stop while I'm behind.

I must say - the original topic I posted still poses challenges for me and I am still not up and running. I want to express my deepest appreciation to ALL of those who have contributed up to this point and further contribution is greatly appreciated. My problem stems from my inexperience in FileMaker and lack of knowledge in the theory and concepts presented so far - then compounded by the fact that I am not going to just "get-something-done". I want to create my solution with a very solid and flexible foundation that I understand wholly.

I still fundamentally believe in the concept and theory of the "Party Model" and "Supertype / Subtype" and am continuing to try to work through to a working solution.

I have uncovered additional sources that use, practice to the "Party Model", or further lend credence to what Len Silverston has presented in his books as the basis for what they are doing, they are...

http://ofbiz.apache.org/

http://www.tonymarston.net/php-mysql/product-prototype.html

I am certainly struggling with the details and the finer points of hooking things up in FileMaker (relationally) and building a method of NOT using a script to create and delete the records if a record is created from a subtype table. I think I have succeeded in doing so in one of my testing files - but I ran out of effort about a month ago due to an extended business trip and the holidays. I also got frustrated and had temporarily had to walk away from it before I went crazy - this is NOT an easy topic.

Corn, your observation about "Name(s)" was well understood - and the challenges it presents when trying to build a data model are more clear than before this thread. While I do not have a solution that I am comfortable with yet, I hope with what you have mentioned and what I am researching will provide an answer that suits my idea of what may be best. I wanted to share an interesting white paper that echoes much of what you pointed out.

I hope to post a testing file I have been experimenting with that shows what I am wrestling with.

Thanks again!

namingnames.pdf

It's good to see that you are still working on this. Don't get hung up on having automatic record creation via relationship. You can override the standard New Record command using custom menus to run any scripted operation and this gives you more flexibility in how records are created. In many cases you need to create records via script because a parent record hasn't been committed yet.

I've have read this thread with great interest as I'm assessing (while slowly learning) the feasibility of employing the product models in Len's v1 book to allow greater flexibility for a system I'm developing. I'll post somthing if/when I have any value to add.

Out of interest I notice there is a new book recently released Data Model Resource Book 3 - apparently focusing on patterns in data modelling.

Ps. Thanks for examples David - great starting point.

Olly

Edited January 20, 200916 yr by Guest

Hi Grubber,

Glad to hear someone else is actively trying to build from the data models as presented in Len's book. Take another look at the second link I posted in my earlier post above that will take you to Tony Marsten web site. He specifically used Len's Product Model as an example of the software he is promoting. It is quite detailed and I was actually building tables as well for the Product portion of my solution.

I would be interested in working on it with you if you see any advantage in doing so.

As for the "new" book, I just read about that myself. I have found volume 1 to be a very difficult read for myself. On one hand it makes sense, on the other, trying to implement in FileMaker leaves many questions for one to deal with.

Good luck! I will be posting a couple of follow-up posts with screenshots and a sample "Party" model FileMaker file to see if I can get some further help here at the forum.

I am posting a FileMaker 7 file. It is my attempt at a Supertype / Subtype table structure based on the party model. It is not perfect by any means - but something I tried to get to a workable stage so others might poke around and help me see where I have gone awry.

As for the "Contact Mechanism" section, I am NOT focusing on that right now. I am hoping to focus strictly on getting the core hook-up of the "Party" model. You will noticed I created an "Animal" subtype table. This was done only so I could see data that would show me it was coing from a subtype table.

I will post a follow-up to this post in a minute that shows screenshots of the screens and the graph. Any input is really appreciated.

Please note, the advice Corn provided is not being ignored, rather I am unclear on how to execute it. I will tackle that after I know what the Super / Sub structure is.

Thanks,

David

dmontano_party_supertype.fp7.zip

Here are the screenshots I said I would post for reference. Thanks for any input.

Hi David. Very interesting. Will take closer look when get a moment (well a few moments).

I'm at every early stages of weighing benefits (flexibility) against disadvantages (complexity) of these models in Filemaker.

As sideline - another interesting resource is the OFBIZ framework (uses party model). Very complex but interesting to see in a production system/framework and has some useful info on wiki...

Nice overview here

Some (scary) Entity Models and Entity Grouping Docs here

Can try a live system based on OFBIZ here

Cheers

Olly

Edited January 23, 200916 yr by Guest
Opps..just seen davids link to OFBIZ above...will leave the links as little hard to find.

To Corn, Michael, David, DMontano et al...

Often within the Party supertype table in SQL databases the attribute NameLegal applies to both subtypes. In the case of an organization subtype, this value is the full legal name, i.e. "Apple Computer, Incorporated", for an individual, it is also the full legal name, i.e. "Philo Thomas Farnsworth, III". For a group, it can be the full name of the group, i.e "Shipping Department". Like wise dateBirth and dateDeath, are also applicable to both subtypes, for an organization the incorporation or incception date, for a person the date of birth.

Party is called a semantic "Is A" relationship pattern, that is, a Party "is a" person OR "is a(n)" organization.

Now it has often been argued theoretically that the semantics of a "Name" are not atomic, that is they can be broken down into parts, which may add N possible additional name variant attributes (fields). When capturing the maximum flexibility of having name parts and recording their significance to the whole, an aggregation structure is used.

An aggregation semantic structure is a "has a" pattern, [1:N] that is a Party "has a" nameComponent. In this way a relation to a nameComponent table can make provision for recording and tracking an infinite variety of name sub variants, nickname, nameFirst, nameSuffix, nameFamiliar, etc and retain their meaning without recoding the application. You would have to enfoirce any data constraints, such as either an organization or a person MUST have a nameLegal.

Since FileMaker cannot index related data,(an unstored calc) the common attributes are usually implemented with a lookup to or from the subtype table to minimize data redundancy. A Database Modeler would consider this structure properly normalized at the Logical Level, even though some redundancy exists at the physical level (FileMaker or MySQL). There are no insert, delete or update anomalies if this is properly implemented.

Also note the exclusive use of the singular form for Entity or Table name, this assist in the modeling process when explaining the structure to users, or developing use cases. It is very awkward to say Parties "is a" person, rather than Party "is a" person, so most modelers use the singular form to enable this expression in English.

Feel free to PM me or email if you need me to clarify anything.

Edited February 6, 200916 yr by Guest

I recently needed to solve a similar problem for a quasi-genealogical database that tracks the history of people's names (given, maiden, changed, nickname, religious, etc.). The actual "listed name" is a combination of SOME of these components (obviously, if they changed their name, the former name is no longer used), in no particular pattern or order (e.g. some married women add their maiden name before their married name, some after).

This can be solved, after a fashion, by marking active names with their ordinal number, and perhaps the following separator character (e.g. space or hyphen).

Now just imagine the following nightmare: suppose there is related table of Events, and suppose we want each person participating in an event to be listed by their name AT THE TIME OF THE EVENT.

Fortunately, a run-of-the-mill business CRM is exempt of all these considerations.

note the exclusive use of the singular form for Entity or Table name

Yeah, well... When I had a shoebox with some cards in it, I wrote "Customers" on the side of it - not "Customer". And I will keep doing so with the electronic version.

Michael-

That may be a workable solution, it's just not a relational one. This means that adding additional parts requires additional code. Now, if you don't understand how to implement a fully relational solution, and based on your posts you may not, then it doesn't mean those are wrong.

Your example unfortunately is not applicable to the earlier discussion because you are addressing a temporal component of the data. The last ten years has seen much work to understand these issues, and they result in a further normalization of the logical model into a Sixth Normal Form, which means that it has no insert, update or delete anomalies with respect to time value of data. We plan to have a podcast on the Adatasol FileMaker Podcast with Dr. Tom Johnston to discuss practical methods to model data that has temporal constraints.

Even Ted Codd (founder of the Relational Model of Data) pointed out that non-relational solutions are possible, however he proved mathematically that they are more difficult to implement, guarantee correct results from, and maintain.

Regards,

Theo

Edited February 6, 200916 yr by Guest

if you don't understand how to implement a fully relational solution, and based on your posts you may not

Yes, that must be it. I was wondering, based on YOUR posts, how long it will take you to get there again:

http://fmforums.com/forum/showtopic.php?tid/186768/

In the event anyone is monitoring this thread - great stuff. Fun reading.

I recently began redesigning an old product inventory db that we use alongside our client/server accounting system. The latter doesn't have BOM and the ability to attach a cad pdf, process sheet, etc.

I implemented a test version for Mr. Graham's technique; seems well suited for our needs. Works well.

Does it make sense to do this in reverse? Create a new record in parent/inner table, then create related record in outer table via the portal key - though this time the 'create related record' would be enabled on child side.

If you have several outer tables, in our case different product groups, the issue would be getting to the proper layout. Im guessing that after new record, that by using script trigger/calc in a field - say "Product Type", this would jump to the associated layout?

The reason for asking about doing it this way - it seems more logical from a user point of view, who has little knowledge of product nuances. If they are entering data from a sheet/list, they of course could be expected to enter data, select pull-down menu options, and let the db guide them to the right areas.

I appreciate my question may be asking a different question than what some of the examples considered here. Or that I didn't possibly understand fully Graham's example.

I'm prepared to be educated, however!

shelley

Hi Shelley,

You bring up some good points that I have had a hard time conveying in forums - what I have been calling "categorizing" data to use as another method of getting Users to where they need to be when entering or searching for data. I find this crucial as most Users are not subject matter experts and a series of drill-downs could educate them and facilitate proper data entry or retreival.

Maybe I misunderstood your goals, but I think you are saying that you would like a User to start at a high-level and drill down to a lower-level to arrive at where the data should reside in one of the "outer" tables.

I too am curious how this might be handled as I think there are cases for data to be entered in both scenarios - outer table first OR inner table first.

I have come to believe that this "inner" and "outer" table structure is a supertype / subtype arrangement, but my lack of formal education has me second-guessing my interpretation of what I am reading or trying to implement.

Here's to rehashing old topics!

Regarding "Name"…

I have just built a little test file with all of the names mentioned in this thread that were used as examples to show how "composing" a name from the names individual parts is not possible due to the multitude of variables.

These things true:

1.

I acknowledge Corn's statements about cultural construct and

Here's to rehashing old topics: Regarding "Name"…

I have been building a test file with all of the names mentioned in this thread that were used as examples to show how "composing" a name from the name individual parts is not possible due to the multitude of variables. For example:

Manuel Luiz Rodrigue Jesus Ponce de Vega

Viet Tran

John Sydney McCain

John Patrick Deval (the donor that must be referred to by "JP" or you get no money.

Cher

Kim Jong-il

These things are true: (In the context of a international database to capture names)

1. The order of name components are a cultural construct (and preference)

2. The number of name components can be 0, 1, or more

3. Some cultures do not recognize family names

4. It is impossible to account for all possible name constructs.

These things I am willing to assume:

1. Those in the population that border or overlap cross-cultural zones are confronted with entering or asked for their "first" name when it is not in a format that is "native" to them at some point in their life, or they are aware of the issue.

2. The majority of name capture formats is either of a Western culture OR an Eastern culture OR a universal single field. In other words, Western culture captures via this pattern: First (Given), then Last (Family); Eastern culture captures via this pattern: Last (Family), then First (Given). Universal: one field.

3. A system can be built to accommodate the majority of these situations.

4. Said system will NEVER capture all constructs.

I hypothesize (always wanted to use that word) that:

1. If you capture the Party Name as a "Preferred Name", it becomes the system of record for the database as a default recording of "Who". This would satisfy the requirement of David Graham's method of auto-record creation of parent record via child table layout by placing the "Preferred Name" field from the Supertype table on the data entry layout based on the child table.

2. Since the one-to-one records have now been created, it is a matter of getting the parts entered into fields that will allow the construction of either Western or Eastern Name constructs.

3. By doing so…

Create 3 fields in the child table

Given Name:

Middle Name:

Family Name:

Create 2 calculation fields User never sees:

Western Construct: Given Name & " " & Middle Name & " " & Family Name

Eastern Construct: Family Name & " " & Middle Name & " " & Given Name

Create 1 field for User selection: Values are: Western, Eastern; Can not be empty

Name Construct Selector Field:

Create 1 more calculation field:

Name Calculated:

If ( Name Construct Selector Field = Western; Western Construct; Eastern Construct)

This seems to be working. I can access the individual name components as needed for other needs such as pulling their "Given" name for a letter. I think it could be further enhanced by using a cross-reference to their country and make the choice on how to display their "calculated" name as needed.

I know it does not include all of the name components, but I think it is a matter of handling those as add on fields. For example, Mr., Mrs. Dr., PhD., etc. can be dealt with in a similar tack - I think.

Then I got to thinking, is there a way to pull the parts out of a composed name instead and avoid the name part fields altogether and reconstruct based on the selection of their culture via the drop down? I think so.

For example:

Manuel Luiz Rodrigue Jesus Ponce de Vega (In one field) could be broken down into the Given, Middle, and Last by using a calc that grabs those positional elements based on what Cultural Construct toggle value was chosen.

Given: Manuel

Middle: Luiz Rodrigue Jesus Ponce de

Last: Vega

But, the problem is that you can not predict if his last name is: "Vega", of "de Vega".

So, maybe a radio box selection (that activates a different calculation) that allows user to grab one more from the end - or one more from the beginning.

For example: If it was not correct in the default calc for Western, have an option to activate another calc that grabs the last two, or last three? Obviously this requires the data entry person to know the name components when they are make such a selection.

This may seem crazy (Hi Vaughn!) but I think this is worth the effort. I say this because I am not the only one to echo that this is an issue in data systems as DJ mentioned.

How's that for rehashing an old topic!

1) You don't need Eastern and Western calc fields. Put the logic in the If() calc field.

2) As you noted, Last Names are not always single words (de Vega, Ortiz y Pino). And neither are First Names (Mary Ellen).

3) One important factor to keep in mind is the Why.

Why split up the Preferred name? What design goals does that accomplish? It's a fun exercise, but what is the utility in automating the parsing of a "Preferred Name" field?

There are certain tasks a solution must be able to perform. Things like formally address a letter, create address labels, make name tags, etc etc. I think it's better to address this issues directly (Name Tag field, etc) than to devise a complex answer to a problem that doesn't exist.

Disclaimer:

I apologize if any this has already been covered in this thread, I didn't bother to reread the whole thing. My current position may even be inconsistent than with what I thought a year ago.

HI DJ,

Thanks for the tip on the calc. - makes alot more sense to keep it in one.

How could you say the problem does not exist? You have stated yourself that one could create "labels" to address it. To address what? To address the problem that we like to communicate with people in a variety of ways. For example…

Hi John

Hello Mr. Turner

Thank you for contributing JT

This award is presented to: Dr. John Williams Smith, Sr.

I know you are very bright - so I say this in the spirit of debate. I do not have a data that supports the assumption that I have that "Most developers combine name components for their solutions" but I am willing to bet they do. Even MS Outlook does so.

Some of my reasons are similar to those who have calculations that add certain name components together - such as: first_name & " " & last_name. If we could generally agree that this sort of piecing together is being used by many developers, than it shows that I am not creating a problem from nothing - rather trying to extend the percentage of the calculated efforts being "more" right than not.

I am not professing that I know the best way. Hardly. I am trying to increase the accuracy of the composition or decomposition of "names". If I can get something to work for most cases and prevent users from entering: "Manuel Luiz Rodrigue Jesus Ponce de Vega" twice, I prefer that quite a bit. If I had to enter it twice - I would be irritated and the likelihood that I will have different entries based on a typo is possible.

In short: I want to enter someone's name once. It does not matter to me which method is employed: separate name component fields - or one field. The goal is to avoid both methods. If I do not have or know all of the name components, it gets entered in as first "heard". Upon later discovery - if it is wrong, it gets adjusted once and we're done.

If we go through the effort to split or combine data (phones, names, salutation, etc.) I think it shows that we do need names in various formats and combinations. If we give control over to the users to "checkbox" select the format - it seems like a plausible solution.

When a direct mail campaign crops up and we need to address it as given, family; and the offer letter needs to use just the given name component in the body of the letter a few times, and then later close with a formal… we obviously need to provide the mechanism to accomplish that.

I am just swinging the bat at the issue. If I could have a single data entry mechanism - personally I would be stoked.

When a direct mail campaign crops up and we need to address it as given, family; and the offer letter needs to use just the given name component in the body of the letter a few times, and then later close with a formal… we obviously need to provide the mechanism to accomplish that.

This is a "western" way of constructing letters. If you were a native Thai (for instance) generating a letter for a Thai audience then the construction may be completely different, as would the requirement for the storage and display of the names.

I don't have an answer. But I can make the observation that what may be the conventional letter format for Western cultures may mot be appropriate for other cultures.

It comes down to the assumption that a person's name is composed of "first", "middle" and "family". In many cultures it is not.

So let's move onto the topic of calculating a person's age and reporting it as a number of years, months, weeks and days.

Yeah, well... When I had a shoebox with some cards in it, I wrote "Customers" on the side of it - not "Customer". And I will keep doing so with the electronic version.

I'd have probably called it "Cards". Go figure.

Okay, call me crazy, but if anyone can help steer my feeble mind in the right direction - I would appreciate it. The question is:

Where would I start in trying to build a Name Decomposition tool that would give the user the ability to select which part of the name is which component: Given, Family, Middle?

To start, I have a field that a data entry person would capture the whole name in one field - called "Name".

For example...

Name: Manuel Luiz Rodrigue Jesus Ponce de Vega

I was thinking about showiing all of the words in the name as a separated list and a checkbox that allows a User to select which one is the "Given" name, which one is the "Family" name, which one is the "Middle" name.

Any ideas?

I knew a guy called Pheonix. That was his real legal name. How does that fit into the "given middle family" model? It doesn't. Is it first name or surname? Is that a valid question to ask?

DJ's suggestion was to forget trying to break down the names into "given middle family" and instead to take a functional approach to provide fields that fit each particular purpose that the database output requires.

BTW when Pheonix went to get some government assistance he was told the database required him to have a "first" and "last" name. So their system knows him as "The Pheonix". (He wasn't short of style, that guy.)

Thanks Vaughn,

Appreciate the input. I made this post especially long just so I could get a witty remark from you that would blast my attempts at such a mind-numbing useless exercise!

I appreciate the complexities. I know there are dozens of ways names are constructed. We also have to ask which component would that be in your example of Phoenix? Here is how I would "like" to handle it.

Every name, including Phoenix, gets entered into the "Name" field. Data entry person has the option of where to drop it in at: Given, Family, Middle.

So in essence,

Step 1. We gather the name as provided and enter it in "Name"

Step 2. Via checkbox (maybe?) We allocate how "we" wish to use it. (Given, Middle, Family)

In the case of "Phoenix", I would choose "Given". If that is what his parents decided to "Name" him at birth, or if that is the Name he wants to be called - so be it. If they, or he, decided not to use the Family Name - then it would not be assigned in that field. So in the case of the previous system you mentioned that had first OR last - I guess what I suggest seems to overcome that obstacle.

I know Names are highly variable - no doubt. I read where a couple in some country refused to name their child and were forced to - they choose some arbitrary alphanumeric string in obvious protest. In this case, I would enter that alphanumeric string in "Name" and via checkbox selection assign it to the "Given" name component field.

1. How about Cher? Enter in "Name" and assign to "Given".

2. How about Kim Jong-il? Enter in "Name" and DO NOT assign it because I have no idea?

3. How about Manuel Luiz Rodrigue Jesus Ponce de Vega? I will choose Manuel as Given, de Vega as Family, and the rest... Middle.When he calls and complains - the facilities are there to correct it. (Could also put a flag that indicates if it was verified)

4. How about Robert Allen Smith? I will go out on a limb and assign as: Given, Middle, Family.

I think anyway you slice it up, data entry is important. But if it could be constrained to make it more black or white, I think it is worth it. Data entry clerk does not have to enter twice? That means you do not get "Manueal" and Manuel".

I also suspect that Manuel sees his Family name reproduced in many different fashions when he has no input into the data entry process (when he is just a prospect from a mailing list). So I think people with names like that live with the fact. However, if Manuel was more important than a 2% response rate, than someone within the "selling" organization would end up correcting it because he either called and complained or the sales rep is keenly aware of his client list and corrects it ONCE.

The point is, I think what I would like to do would work for my needs.

Which brings this observation: If you can not determine the name components, you should be left with the option of not assigning it. So assignment has to be optional. But, in a Direct Mail campaign, if you were using first name - ole Kim Jong-il's would be blank. Unless there is a calculation that says, If Given is blank - use "Name". Name would have to be mandatory value and Given, Middle, and Family must all be derived and not allowing entry into field. This would keep those fields from gettinig out of sync.

I agree "First, Last" are not good labels for Name components. However, it is harder (though not impossible) to argue the viability of:

Given (The Name someone was Given and commonly referred to by)

Middle (ALL of the other names Given to them - not commonly referred by)

Family (The Name they inherited)

Pardon the lack of a GREAT definition - but I am willing to settle on those definitions.

Now the positional issue (First, Middle, Last). They "appear" to me to be a cultural construct - except for middle which is just a bunch of other names you might be given). However, having the one "Name" field that is mandatory does tell us how they want it presented - as prescribed when entered. As for all of the other "Names" such as Title, honorific, blah, blah, blah... I think they can always be handled separately and in separate fields. Same for Nicknames.

And as far as the big contributor who wishes to be addressed by just "JT". We can stick him in a "MUST REFER TO HIM AS" field that trumps all for presentation. But you still need his full name and separate components. If it is not provided - then he stays right in the "Name" field.

Sooo, now that I am convince I have found the holy grail of names, off to post maybe in the calc forum to see if someone can help me figure out how to create this thing. Then I will discover that it is not what I had hoped for and will come back here with a new found philosophy on names! Right?

As always... thoughts, comments, and blasts are appreciated.

David

I don't see that what you propose solves the problem better than simply having three fields (Given, Middle, Family).

In the case of JT, the big donor, why would you capture information you're never going to use (legal first name)?

Build your entry system to reflect the needs of the solution.

If all your db does is print nametags, with the name a conference attendee wants to be addressed as, then you have one name field.

If you also need to mail a letter thanking them, have two fields one for name tags, one for mailing address.

If you want to print a list sorted by Family Name, you can have three fields, Nametag, First, Last.

If you're concered about repeating data entry, you can either have Name Tag auto-enter the First Name field, or have a button that does it (like Shipping and Billing addresses for online ordering).