On Fri, 19 Sep 2014 17:40:52 +0100 Simon Slavin <slav...@bigfraud.org> wrote:
> > 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 > > For 1, why are you selecting by last name ? The person I named above > has the surname "Nielsen Hayden". Which would you be selecting and > why ? Some small confusion here. By "last name" I mean surname, not "last string in name delimited by white space." First name: Wernher Last name: von Braun You say it shouldn't sort by "von", but that's a cultural choice, too, cf. the Manhattan phone book. > For 2, match the string entered with the beginning of the 'name' > field, probably using LIKE and a percent character. So you're assuming the beginning of the "name" column -- entered "however they want" is the first name. I guess in a literal sense that's true. You're going to have trouble with Fran, Frank, and Francis, though. That you have to use pattern matching for equality suggests a problem, if not an error. > For 5, are you telling me you don't know how you want the name > sorted ? No, 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. 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. Your suggestion essentially amounts to "names are not decomposable, so keep one version for the user and one for the system." 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'm aware that different cultures use a variety of systems. When people in those cultures encounter computerized databases and large bureaucracies, they deal. They adapt. Family tradition might reckon 17 names and three hyphens, but the birth certificate will hold only three or so, and that forms the legal basis for the baby's identification in society. You don't have to go very far before the straightjacket doesn't fit, but for practical purposes it doesn't matter. I know a woman "Peg" who was born "Margaret". Which name appears in which database depends on its purpose. In the email system she's Peg; on her paycheck it says Margaret. I imagine the application exists that requires the name be rendered just as the person prefers. Usually, though, databases are used for sorting, searching, and (most important) identifying. Those purposes are better served by distinguishing conventionally among the parts of a person's name. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
