On 20 Sep 2014, at 7:42pm, James K. Lowden <jklow...@schemamania.org> wrote:
> I'm saying more than one sort order is often needed. If you don't
> distinguish among the components of the person's name, you can't sort
> by those components.
I don't understand why anyone would want to sort on firstname or middle name.
I suppose having them indexed might be useful for searching, but apart from
very specific census questions like "How popular were certain names in the
1990s ?" I see no reason to do it.
> You offered the OP sweeping contrarian advise on how to represent names
> in a database, referring him to one odd source having nothing to do
> with databases.
That one odd source is so widely quoted nobody else has bothered to write
anything on the subject. A search on "People have exactly one canonical full
name" gives me 1,830 hits and that's just the quotes rather than the links and
references.
> Your suggestion essentially amounts to "names are not
> decomposable, so keep one version for the user and one for the
> system."
Sorry, I don't think I got that across effectively. If I make up a database
that stores names, I'm storing them pretty-much for two main reasons: to know
the name and to sort by name on displays and printouts. So I keep one column
for the whole name ("to know the name") and one for the name in the order I
want to see it sorted ("to sort by names"). Two things you need, make two
columns. I see no reason to split name up into three artificial units.
Searching by name is going to require LIKE and Soundex or something like it.
I've seen a database where a third column was used to store the soundex
encoding of the full name. (Might have been something else like Soundex. It
was a long time ago.) In SQLite you could probably feed that column with
TRIGGERs.
> Given the thousands of databases out there that use first,
> middle, and last names as columns -- with apparent success -- and
> theoretical and practical problems arising from your alternative, I
> suggest your advice is ill-founded.
I suspect that those thousands of databases had their fields defined in the
1970s, along with checking to see that all three fields weren't blank and
didn't have anything but letters in them. And that such things would only be
done in the US. Certainly anyone who designed a database that way in England
(where I live) would have to immediately write a page full of instructions
about how to squeeze many names I see around here into those three fields.
If you want to assess their success, ask yourself how many times you've seen
NO-MIDDLE-NAME in official records. Or someone who apparently has the lastname
of OMALLEY. Or someone who apparently has a lastname of Al-Haj and is
therefore sorted into the 'A's.
Anyone who worked for a big company these days and created such a database
should get called in and told to do it again properly.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
