Names and more Names

[Paul Purvis]

fyi

free software to parse names
http://www.semaphorecorp.com/nc/namechop.html

free software to set/tag gender of first name
http://www.semaphorecorp.com/gender/gender.html

[Michael Mattias]

The whole thng about multiple addresses - well, multiple anythings - is, as soon as you decide on a hard limit, the user 'discovers' he forgot to tell you about that 'other' one he needs.

As soon as someone tells me they need 'n' of something, I design either for 'n+2' or 'as many as you want.' Hard limits set off warning bells in my head.

(The other big warning bell? "This is only a temporary program.")

[Rick Kelly]

That makes a Customer table, an Address table, and an Address Type rules table, some indexes, foreign keys if your database does 'em. As the Address table may also serve for addresses of entities other than Customers, attention to the keylengths, types and value ranges of the Customer and other addressable entities will be rewarded.

This whole thread is indeed becoming more interesting.

In my schemas I allow multiple, time and purpose sensitive addresses. (I do the same basic thing with other items such as phone numbers, contacts, etc) There is no hard limit although I have yet to see any clients manage more than a half dozen or so per person/organization and there is a default primary value for each. I keep separate columns of address lines - usually about 3-4, city,location,postal code, and country are also kept in discrete columns. Postal and country columns are backed up by look-up tables. I usually have the postal code first in the data entry tab order so I can look it up and fill in other form fields and auto tab forward. When data is arranged to be saved, I'll build a final mailing and apply some rules based on the country (i.e. Do i show the country name?) and save it as well for display/printing purposes. I've searched different country postal authority web sites to see what outgoing mail address formats they support for both domestic and overseas forwarding. For all this to work, I also need to have available my client's postal address.

I could do the same thing for names and just avoid all the parsing hassles and I'm looking at how best to do that as well. I'm keystroke/mouse click sensitive in my interface designs and always looking at minimizing both. I'm a believer that data entry basics should not use the mouse unless absolutely necessary. I've found that judicious use of the enter and tab keys and some intelligence working while data entry is going on is both intuitive for a majority of folks and increases the rapidity of entry.

In schemas where both individuals and organizations are maintained separate tables are used and relationship rules between the two defined.

[Michael Mattias]

That makes a Customer table, an Address table, and an Address Type rules table, some indexes, foreign keys if your database does 'em.

Yes, it does. And let me tell you, the Point.Man ERP system has 'em all, and it is really, really powerful and flexible when it comes to specifying - and searching for - multiple addresses for any 'entity'

[Chris Holbrook]

That makes a Customer table, an Address table, and an Address Type rules table, some indexes, foreign keys if your database does 'em. As the Address table may also serve for addresses of entities other than Customers, attention to the keylengths, types and value ranges of the Customer and other addressable entities will be rewarded.

[Michael Mattias]

I like the physical and mailing address to be separate even though they might be the same.

Given your druthers, better I think to design in terms of 'function'

eg Ship To (many possible), Bill to, Remit To, contact (many possible), department (many) etc etc etc.

Than again, the druthers are often gone when your turn comes.....

[Paul Purvis]

Also Rick, there is a big question too in that do you split out address lines into separate fields.
There are some big benefits there as well in trying to keep addresses in a standard format and when it comes to cleaning up addresses or matching addresses or verifying that an address exist is just as much challenging.
It is wise to keep a addition fields for the whole address and another city state zip to allow placement for other programs to standardize your addresses for mailing purposes.
I like the physical and mailing address to be separate even though they might be the same.

[George Bleck]

Hey not my choice! I love walking away making people happy. There's a sense of accomplishment. I.T. is a business, it all revolves around the dollar, euro, yen, etc. Big management made this decision, not the worker bees like me.

[Michael Mattias]

I.T. is not supposed to care if clients are "happy", they are supposed to deliver within the agreed upon contract.

And you wonder why the IT department got out-sourced to Bangalore?

[Paul Purvis]

Just a fyi
here are some lengths of variables that have severed us well in south louisiana for local use.

TITLE$=5
FIRST$=20
NICK$=20
MIDDLE$=20
LAST$=20
SUBFIX$=5
ADD1$=35
CITY$=19
STATE$=2
ZIP$=10

i would increase the address line to 50 next time and the city could be increased some for other areas.

[George Bleck]

I.T. is not to "serve" the clients... that represents a slave/master relationship That is the I.T. of old.

The new I.T. is here to offer a SPECIFIC SERVICE at an AGREED UPON service level, at a SPECIFIC COST. As an entity, I.T. is to continually improve their internal methodologies so as to lower their internal costs to maximize reinvestment to continue the cycle. I.T. is not supposed to care if clients are "happy", they are supposed to deliver within the agreed upon contract.

Most OU's used to consider I.T. to be a black hole that money is poured into, they themselves forced I.T. into this position. They wanted I.T. to become more transparent and expose their costs. Sadly, the days of I.T. "helping" clients is over, we're are now a business like everyone else. Customer satisfaction rarely comes into play anymore, now it's a matter of a numbers game, did we meet contracted agreement at the lowest cost? Then we win, even if the customer was not "happy".

[Michael Mattias]

>I feel their is no excuse for getting a customers name wrong

Of course there isn't. This is Business 101, The Customer Is King.

However, when your underlying database design tolerates ambiguity, seems to me the designer of same forgot the I/T department's job is to SERVE the business requirements.

MCM

[Chris Holbrook]

Knuth, I think the greatest challenge lies in deriving the components from an existing freeform "name" which could be one of several things, each capable of being formatted originally in several different ways. In my experience it is non-trivial.

I don't know, and am not likely to know, the details of Rick's requirement, so my comments are of a general nature.

Consider, for example, that the name may refer to an individual, a couple, several people, a family, a titled person, a company, a non-commercial organisation, the estate of a deceased former customer, a substitute for the name where it is not known, a government department.

Then think about the many different ways in which each of those "names" might have been captured, possibly over a long period of time, possibly with accretions of comments from operators (yes, it happens!) ranging from the helpful to the unmentionable.

If the volume of data permits, then it is best to "convert" existing names to structured names manually, preferably not using temporary staff!

If an exclusively automated conversion is used, trouble can be expected. It is also wise to ensure that its testing is signed off by the customer-facing part of the organisation!

[Knuth Konrad]

Which is the correct interpretation?

Mr Van Anson would definitely know.

Yes, Mr. Van Anderson would indeed know.

As Paul stated, if you ("you" as in "the one inputing the data/having contact with Mr. Van Anson") haven't taken the time to ask him, but entered your "best guess" into your database, you shouldn't be surprised if things start to get messy. As the old saying goes: garbage in -> garbage out.

One the issue itself, why not create a virtual column on the fly, something like

SELECT (Salutation + ' ' + FirstName + ' ' + Initial + ' ' + LastName) As DisplayName

[Eric Pearson]

Rick --

An interface similar to Google Advanced Search (here) might provide a solution for you. As you type, it parses and displays the final sub-field results in real time. You could allow the user to type a name in a single field, and your program would auto-parse and display the first name, last name, middle initial, etc. Then, if the user edits/corrects one of the sub-fields, the main string would be modified. Only when the user clicks OK (or whatever) would the final data be accepted.

> I'm surprised at this offshoot of "unsolicited" feedback. I know database theory as well as any poster

Don't take it personally. There's no way for other posters to know your level of experience, so everybody pitches in as best they can, based on their interpretation of your question and their level of experience. That's the nature of most peer-support forums.

-- Eric

[Christopher Carroll]

One successful way to avoid unsolicited responses is to not post.

A paper that provides a clear description of the problem with recording names of people in databases is written by IBM at http://www-935.ibm.com/services/uk/c...osnameisit.pdf. While it does not offer any solutions, the conclusions are worth giving some thought. The penultimate paragraph is of relevance to this forum topic.

[Paul Purvis]

I feel their is no excuse for getting a customers name wrong. Personally, I am insulted when a business does not take the proper time to get a name correct. Because i have seen people put in names and not follow the standard conventions you want for a one liner name input, I feel it is imperative to allow for plenty space in databases for naming. I have had to spend days and nights on end to make corrections before moving to a new main program from a old system. I like also having an alternative single line for the whole name. If the single line is blank, then you can build a name from the individual fields. It is also good to have a field for nick names. When it comes to names, i want our people to use the christian name(legal name). By splitting up names, it is easier to write programs to check for misspelled names, titles, and subfixes.
It is just a lot more simplier to have names split into fields than to stay on somebody's butt for entering names in a correct format. Multiple fields help workers police themselves.


I have also seen in Louisiana where you have to please the dept of public safety (DMV) and have papers with names that are wrongly typed just because the DMV has the name entered wrongly spelled names on their systems and when processing papers, your papers have to match the wrongly spelled names as on the DMV, no matter of the actual real name.

[Rick Kelly]

Rick,

I would advise using a distinct field format when storing names belonging to individuals. You will definitely encounter some ambiguous free format names which can be parsed in a number of ways.

Your proposed 'splitting' processing scheme would either need to identify the ambiguity and require some further input, or make a decision/guess about which is the correct interpretation (and accept that sometimes it will be the wrong decision).

For example, consider the name 'Mr Van Anson'.

You could separate out the name elements as

Title: Mr
Forename: Van
Initial: V
Surname: Anson

or

Title: Mr
Forename:
Initial:
Surname: Van Anson

Which is the correct interpretation?

Mr Van Anson would definitely know.

Regards,

David

I agree there is ambiguity and your example is what I had in mind in the original post where I mentioned "double names". This may get too complicated as in the USA we have such a diverse representation of naming styles from around the world. A compromise that comes to mind is to apply the algorithm and include the results on the input screen for manual tweaking. It's the data input effort that my clients are most focused on as they prefer to just start typing away without using drop-down lists for suffix/prefix/titles/etc and tabbing to different fields.

Using your example, the main ambiguity is whether the first name is even entered and "Van" could be a double-name flag or a first name and after entering "Mr. Van Anson" the screen could show:

Last Name: Van Anson
First Name: <blank>

I believe I could get it right a large percentage of the time and tabbing a few times would get you to the address portion which is another issue altogether with postal codes/country codes and allowing multiple, time-sensitive alternates. (Lots of snowbirds here in Alaska that live in warmer locales during the winter)

Rick

[Chris Holbrook]

Whether one provides discrete fields on a data entry form and builds a blended result or takes the blended result and breaks out and builds the discrete building blocks the ending results can be identical.

Want to bet? David has given you just one simple example of ambiguity. There are many more. It doesn't matter how good you are at database theory. What will happen is you will have to flag some names for manual cleanup. You will also get some complaints from ones which looked clean but weren't. Depending on your budget and location, there may be directories which you can reference for a surname check.

Yours unsolicitedly...

[David Warner]

Rick,

I would advise using a distinct field format when storing names belonging to individuals. You will definitely encounter some ambiguous free format names which can be parsed in a number of ways.

Your proposed 'splitting' processing scheme would either need to identify the ambiguity and require some further input, or make a decision/guess about which is the correct interpretation (and accept that sometimes it will be the wrong decision).

For example, consider the name 'Mr Van Anson'.

You could separate out the name elements as

Title: Mr
Forename: Van
Initial: V
Surname: Anson

or

Title: Mr
Forename:
Initial:
Surname: Van Anson

Which is the correct interpretation?

Mr Van Anson would definitely know.

Regards,

David