Been racking my brain: Supertype / subtype

[comment]

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.

[Dave Graham]

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

[dmontano]

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

[Dave Graham]

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.

[grubber]

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, 2009 by Guest

[dmontano]

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.

[dmontano]

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

[dmontano]

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

[grubber]

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, 2009 by Guest
Opps..just seen davids link to OFBIZ above...will leave the links as little hard to find.

[teka]

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, 2009 by Guest

[comment]

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.

[teka]

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, 2009 by Guest

[comment]

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/

[barkingsheltie]

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

[dmontano]

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.

[dmontano]

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

[dmontano]

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!

[David Jondreau]

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.

[dmontano]

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.

[Vaughan]

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.

[Vaughan]

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.

[dmontano]

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?

[Vaughan]

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.)

[dmontano]

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

[David Jondreau]

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).