Most systems that encompass non-western style names will use different terms:
formalname and familyname in preference to firstname and lastname. FirstName, MiddleName, LastName imply ordering which does not necessarily hold. Calling them FormalName, AncestorName, FamilyName more aptly describes the use of the various bits without implying the ordering. Then a CommonName field is used to hold the display variant as the person would usually prefer to see it (which may be Formal Ancestor Family as in western style names, Family Formal Ancestor as in most Eastern style names, or Family Ancestor Formal as used in some other Eastern cultures). Often a FamiliarName is provided so that the name by which a person wants to be addressed can be stored. This is necessary even for proper handling of western names where, for example, someone might want to use: formalname = Robert ancestorname = Richard familyname = Smith familliarName = Dick CommonName = R Dick Smith >-----Original Message----- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of James K. Lowden >Sent: Friday, 19 September, 2014 08:58 >To: General Discussion of SQLite Database >Subject: Re: [sqlite] An order by problem, maybe a bug? > >On Fri, 19 Sep 2014 02:02:30 +0100 >Simon Slavin <slav...@bigfraud.org> wrote: > >> By the way I wanted to warn you about starting any project with first >> name, middle name and last name fields. This leads to problems, and >> I would go to some lengths to avoid it if possible. It would be >> better to provide two columns: >> >> name (their name, however they want it to be shown) >> nameInSortOrder (their name, in whatever order you feel it should be >> sorted) > >I wonder what "problems" you're talking about. Do you think the IRS, >the Social Security Administration, the DMV, the passport agency, your >birth certificate, and your local bank are just doing it wrong? I've >filled out address forms in four languages I can think of, and every >one had a place for my first name and last name. I've never seen one >that seems like it would present a problem for "Patrick McKenzie". > >Problems arising from the schema you suggest: > >1. select by last name >2. select by first name >3. duplicate detection[1] >4. "however they want" is unknown and idiosyncratic >5. "whatever order" may be more than one > >If you print a list of "however they want" ordered by "whatever order", >the result will look very strange and be hard to use, because the sort >order will not be apparent in the listing. > >The issues raised in the linked page apply mostly to application >development. The database's job is to capture the facts. Just get the >person's name -- in its discrete parts -- in the database and be done >with it. > >--jkl > >[1] Yes, I know names aren't unique. Duplicate detection isn't only >about rejection; it's also about noticing similarity to avoid, say, >assigning a new identifier to the same person. >_______________________________________________ >sqlite-users mailing list >sqlite-users@sqlite.org >http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users