Names and more Names

[Michael Mattias]

>and I'm surprised at this offshoot of "unsolicited" feedback

You've only been a member for a year. You'll get used to it.

[Rick Kelly]

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. The searches/indices, etc do not involve the blended, or in this case, the full display name of an individual or organization but rather the parts of a name that matter. Just as a soundex calculation can be done and stored for later use, so can the portions of a full name. It's the same basic process although in this case it's a lot more complicated.

It was a simple question originally and I'm surprised at this offshoot of "unsolicited" feedback. I know database theory as well as any poster and am also not adverse to bend or even shatter it when it suits my purposes. Real world and academia are not the same.

[Michael Mattias]

A 'freeform text' field is not really suitable for searching except with a 'like' or 'contains' operator, and that's aside from performance issues.

Also not suitable for searching are variable-location and/or length substrings of single fields... but that is kind of redundant since that by definition means the data are not normalized.

[Christopher Carroll]

You can do that if you want, but it is disregarding the First Normal Form (1NF) in a relational model database: All attribute values are atomic.

From C.J. Date, "An Introduction to Database Systems":

Hence, the one-word answer to the question "Why do we insist on normalization?" is simplicity - simplicity, that is, in the basic data objects we have to deal with, which leads in turn to corresponding simplifications everywhere else in the system.

Better, if possible, to educate the clients and record the data properly.

One option is to have separate columns for Last Name, First Name, Prefix, Suffix, etc.; then a separate one for a free-flow name. This can be populated manually by the user, or by program action.

It may not matter in Alaska, but in several Asian countries, the Surname (Last Name) is always written first, not last. Not having an atomic data structure makes it impossible to do anything meaningful with name data if there is a mix of naming structures within it; so there are very practical reasons to ensure the data is normalized.

[Gösta H. Lovgren-2]

With feedback from clients, I have concluded that when it comes to handling persons and organizations in databases, folks like to have a one-line free form column when editing names and they want this to display/print exactly as entered.

Is it possible to have and additional field/column(s) just for that purpose? ie
Type Stuff
Name as string * 25
Address as string * 25
...
Display_Personal as string * 100 '
Display_Formal as string * 100 '
End Type

===============================
"A modest man
with much to be modest about."
Winston Churchill
===============================

[Chris Holbrook]

It would have to be some snippet to do the job properly.

Most people are used to being wrongly addressed, which is a comfort. However, those who are hardest to get right are the most vociferous and easily offended.
I can recommend Debretts as a useful reference when deciphering UK titles and forms of address. Possibly irrelevant - your profile does not reveal geography.

[Rick Kelly]

With feedback from clients, I have concluded that when it comes to handling persons and organizations in databases, folks like to have a one-line free form column when editing names and they want this to display/print exactly as entered. This complicates searches a bit as it now becomes necessary to split apart those names into discrete fields/columns to be able to use things like SQL "Like" and to distinguish common prefix/suffix, connectors and double word names accurately while stripping out periods, comma's, extra white space, etc.

Before I get into building some code to do this (which I'll post)...Has anybody any working code snippets?