Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/21 17:18, James K. Lowden wrote:

...to get web payment forms to allow, for the love of God, spaces in credit 
card numbers. --jkl


Now there's a worthy cause. Ditto for phone numbers (though they mostly are more lenient today). Also to allow hashes and dashes in 
the address field. I spent quite a while on the phone to some American online company trying to buy stuff using a credit card for 
which I needed to supply my address for verification purposes... and in some countries we have addresses like 19A 
Hem#Strassen-34'B(N), etc.  So as long as some of those characters are not allowed in the field, I cannot possibly reproduce an 
address that matches my bank's version of it, and needless to say, cannot pay anyone with such input field restrictions.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Tim Streater]

On 21 Sep 2014 at 16:18, James K. Lowden  wrote: 

> Really?  HM Revenue and Customs doesn't require you to distinguish
> between your given and family names once a year?

Search me. As long as I get my tax adviser to file my tax return once a year, 
and send them dosh twice a year, they don't seem to care.

> Your drivers license doesn't say "Surname" on it anywhere?

I don't have one of them. I have a "Driving Licence", and no, the word 
"Surname" does not appear on it. What does is "1", followed by my surname, and 
"2" followed by my first and middle names (and various other fields).

> I hope we've finally exhausted this topic.  Now, if you really want to
> usefully direct your righteous outrage, please join me in my petition
> to get web payment forms to allow, for the love of God, spaces in
> credit card numbers.

Well I'll vote for that, as it's trivial to implement. You can go to 
http://epetitions.direct.gov.uk and start it there.



--
Cheers  --  Tim
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Sat, 20 Sep 2014 20:21:29 +0100
Simon Slavin  wrote:

> > 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.

I think you got your point across just fine.  "no reason to split name
up into three artificial units" is the same as "names are not
decomposable".  

The units aren't artificial, else the history of Europe would surely
have been different.  Names if nothing else have legal meaning.  There's
a case in court in Tennessee currently because, in that state,  the
couple can't put just any old thing in the "last name" of the birth
certificate.  

I can well appreciate that might not matter to your database, which is
really my point.  What we're really dealing with here is a question of
problem domain: what does the name *mean* in the context of the
database in question. For you, evidently, it's the string by which its
owner identifies himself.  You take what he gives you, and you
reproduce it once for sorting purposes.  Because not every database has
such relaxed needs, your advice is not generally applicable.  

You say, 

> I don't understand why anyone would want to sort on firstname

I guess you've never produced a club membership or other informal
listing by first name.  I thought that was pretty common in those clubs
I've heard about over there, with the Chesterfield couches and brandy
and cigars by the fireplace, old chum?  

That also suggests you've never had to reconcile two databases in which
the people's names were the only identifying information.  Some years
ago a friend of mine integrated 50 separate state databases of doctors
for the AMA.  Much hilarity ensued, as I'm sure you can imagine.  The
job would surely have been harder if last names hadn't been
identified.  

> 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.  

Really?  HM Revenue and Customs doesn't require you to distinguish
between your given and family names once a year?  Your drivers license
doesn't say "Surname" on it anywhere?  

> 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.

These are not necessarily database limitations or database design
issues, as you know.  Just because some COBOL system running IMS in
1975 didn't admit nonalphanumeric data, doesn't mean that in 2014 we
can't distinguish last name from first.  

> 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.

The day that happens, the lunatics will have been put in charge of the
asylum, because "properly" will have lost all meaning.  

I hope we've finally exhausted this topic.  Now, if you really want to
usefully direct your righteous outrage, please join me in my petition
to get web payment forms to allow, for the love of God, spaces in
credit card numbers.  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[RSmith]

On 2014/09/20 23:23, Simon Slavin wrote:

...calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel


Oh you know him? We go way back... old Tim Biscuits we used to call him. It was fun watching the undertakers figure out how to get 
all that on his gravestone after that plan to blow up the empire state went terribly awry in the test phase. People still mistake 
that stone for a karaoke machine.



___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 11:09pm, Mark Halegua  wrote:

> I'm going to have a separate table for pennames.  Lester del Rey will be last 
> name del Rey, 
> first name Lester.
> 
> My problem will be the following:
> 
> Therer are a number of writers who used several non de plummes, however there 
> are some 
> credited authors who are house names (Maxwell Grant is one, with no fewer 
> than three 
> writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
> Tinsley, and 
> others) and it is this I'm having trouble normalizing.  Thinking for those 
> house names I'm 
> going to have to refer to the story or issue to fully credit the corect 
> writer,  Any thoughts 
> here are welcome.

I suspect that you may need a many-to-many relationship.  You have already 
listed a case where one author used many pen-names, and a case where different 
people wrote with the same pen-name.  To make it worse, some works are written 
by someone writing under their own name.  Inconceivable.

If you need to capture the whole truth in your data columns, taking into 
account all combinations which happened, no matter how rare, then each work 
published needs two fields: real author and pen-name.  Each of which, of 
course, should be foreign keys into two other files.  Often the texts shown in 
those two fields will match each-other.  But one might be an informal version 
of the other, for example "Francis H. Burns" in the person file but "Frank 
Burns" in the pen-name file because that's the way the name is usually printed.

Another way to capture the whole truth would be to establish the many-to-many 
relationship between people and pen-names, and have each work point at a record 
in the middle file of that relationship.

If you only need to capture 99% of the truth, and can insert a note for a few 
works explaining anything unusual, then it may be possible to store only the 
pen-name for each work.  In your pen-name file you can list what would normally 
be just the name of the one person who used that pen-name, however you might 
want to establish a format where there could be multiple names separated by 
commas or semicolons or something.

I hate making choices like these.  Good luck picking one which turns out to be 
the one your users will find most convenient.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

On Friday, September 19, 2014 08:07:06 PM Simon Slavin wrote:
> No, no he's just working on US Pulp Magazines.  All pulp writers have
> traditional names.  He's not going to have any trouble.
> 
> Except, of course, with Daniel Keys Moran.  Who doesn't use his first name
> except when writing.  And Marion Zimmer Bradley, whose name should be
> sorted under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted
> under 'B'.  And Margaret St. Clair whose name is correctly pronounced and
> sorted as if it was Margaret Sinclair.
> 
> And Lester del Rey.  Whose name should be sorted under 'd' (his wife was
> Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was
> Ramon Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y
> Alvarez del Rey y de los Uerdes.

I'm going to have a separate table for pennames.  Lester del Rey will be last 
name del Rey, 
first name Lester.

My problem will be the following:

Therer are a number of writers who used several non de plummes, however there 
are some 
credited authors who are house names (Maxwell Grant is one, with no fewer than 
three 
writers writing the Shadow as Grant - Walter B. Gibson 285 of the 325, Ted 
Tinsley, and 
others) and it is this I'm having trouble normalizing.  Thinking for those 
house names I'm 
going to have to refer to the story or issue to fully credit the corect writer, 
 Any thoughts 
here are welcome.

> 
> Of course, the author Eando Binder was actually two people (Edward and Oscar
> Binder) but that doesn't count.
Actually, it does, but it will be taken care of in the pennames table.  And, 
for a period, one of 
the Binder brothers left and the remaining Binder continued to write as Eando 
Binder.

Mark
 
> Simon.
> ___
> 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

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 9:42pm, Petite Abeille  wrote:

> Your last name contains invalid characters
> http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Ah yes, John Graham-Cumming.  One of those sneaky non-standard-format foreign 
names.  Probably a terrorist.

On 20 Sep 2014, at 9:27pm, John Hascall  wrote:

> The problem with your suggestion of 'two uses => two fields' is that no 
> sooner do you do that then somebody comes up with additional uses, for 
> example, formal greeting, informal greeting, the appropriate form for 
> government form X123, and so on

Banks I've worked with include a field called "Salutation" which says things 
like "Dear Ms. White" or "Dear Mrs. White" depending on the account-holder's 
preference.

Financial and legal institutions, by the way, are the toughest at getting this 
right.  They have a legal requirement to know the account-holder's 
commonly-used form of name.  Not some twisted abbreviated version of it.  If 
the account-holder calls themself Tarquin 
Fin-tim-lin-bin-whin-bim-lim-bus-stop-F'tang-F'tang-Olé-Biscuitbarrel that's 
what they have to have in their database.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[John Hascall]

The problem with your suggestion of 'two uses => two fields' is that no sooner 
do you do that then somebody comes up with additional uses, for example, formal 
greeting, informal greeting, the appropriate form for government form X123, and 
so on

John Hascall
IT Services
Iowa State Univ.

> On Sep 20, 2014, at 2:21 PM, Simon Slavin  wrote:
> 
> 
>> On 20 Sep 2014, at 7:42pm, James K. Lowden  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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Petite Abeille]

On Sep 20, 2014, at 9:21 PM, Simon Slavin  wrote:

> 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.

Along these same lines:

Your last name contains invalid characters
http://blog.jgc.org/2010/06/your-last-name-contains-invalid.html

Falsehoods Programmers Believe About Names
http://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/

How do you like the vCard specification?

http://tools.ietf.org/html/rfc6350

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 20 Sep 2014, at 7:42pm, James K. Lowden  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

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 17:40:52 +0100
Simon Slavin  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

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 11:42:26 -0700
Roger Binns  wrote:

> You do realise there are more people in the US than just those born
> in the country with good old fashioned roman alphabet 26 ascii
> letters?  

Yes.  Did I mention ASCII?  

--jkl
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Klaas V]

Simon wrote about parts of names like e.g. the Dutch/Flemish 'van'

They should definitely not be capitalised

Is not always true. Especially in northern Belgium names are often spelled like 
Van (often even connected with the last name)  and I did personally the same to 
see which of the two e-mail clients I used at work so the NOCASE tag may cause 
inconsistencies as well.

Anyway to split a name in two, three or maybe even more parts is not a bad 
idea. The solution with a number of commas is a very good one.

Kind regards | Cordiali saluti | Vriendelijke groeten | Freundliche Grüsse,
Klaas `Z4us` V  - OrcID -0001-7190-2544
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 7:42pm, Roger Binns  wrote:

> On 19/09/14 07:58, James K. Lowden wrote:
>> 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? 
> 
> You do realise there are more people in the US than just those born in the
> country with good old fashioned roman alphabet 26 ascii letters?  And yes
> they do get it wrong:

No, no he's just working on US Pulp Magazines.  All pulp writers have 
traditional names.  He's not going to have any trouble.

Except, of course, with Daniel Keys Moran.  Who doesn't use his first name 
except when writing.  And Marion Zimmer Bradley, whose name should be sorted 
under 'Z'.  Though, of course, Edgar Rice Burroughs should be sorted under 'B'. 
 And Margaret St. Clair whose name is correctly pronounced and sorted as if it 
was Margaret Sinclair.

And Lester del Rey.  Whose name should be sorted under 'd' (his wife was 
Judy-Lynn del Rey).  And who normally claimed (falsely) that his name was Ramon 
Felipe San Juan Mario Silvio Enrico Smith Heartcourt-Brace Sierra y Alvarez del 
Rey y de los Uerdes.

Of course, the author Eando Binder was actually two people (Edward and Oscar 
Binder) but that doesn't count.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Roger Binns]

On 19/09/14 07:58, James K. Lowden wrote:
> 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? 

You do realise there are more people in the US than just those born in the
country with good old fashioned roman alphabet 26 ascii letters?  And yes
they do get it wrong:

  http://www.usatoday.com/story/news/nation/2013/09/13/long-last-name/2810603/

Or try having the name Chloé in the US.  Heck my friend Stephane had a hard
enough time as everyone assumed he couldn't spell his own name, and
corrected it to Stephanie!  And his name is actually Stéphane, but don't
think any of those agencies you listed would acknowledge that.

Roger
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[John McKown]

On Fri, Sep 19, 2014 at 11:40 AM, Simon Slavin  wrote:
>
> On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:
>
>> On Fri, 19 Sep 2014 02:02:30 +0100
>> Simon Slavin  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.
>
> First, if you automatically generate letters, a letter to "Patrick Nielsen 
> Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His 
> surname is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" 
> should be addressed "Dear Mr Fitz William".
>
> Then, if you sort the names into order, you're going to find "Patrick Nielsen 
> Hayden" under H, whereas you should find him under N.
>

This is an interesting problem. Which the Unicode people addressed
with the "non-breaking space" character of U+00A0. Of course, entering
this particular space character is not as simple as just hitting the
space bar.

-- 
There is nothing more pleasant than traveling and meeting new people!
Genghis Khan

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 3:58pm, James K. Lowden  wrote:

> On Fri, 19 Sep 2014 02:02:30 +0100
> Simon Slavin  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.

First, if you automatically generate letters, a letter to "Patrick Nielsen 
Hayden" is going to start "Dear Mr/Mrs Hayden". That's incorrect.  His surname 
is "Nielsen Hayden".  Similarly a letter to "Edward Fitz William" should be 
addressed "Dear Mr Fitz William".

Then, if you sort the names into order, you're going to find "Patrick Nielsen 
Hayden" under H, whereas you should find him under N.

> 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 ?

For 2, match the string entered with the beginning of the 'name' field, 
probably using LIKE and a percent character.

For 3, check to see if either field is a duplicate.

For 4, type in their name however they wrote it on the form you're copying.

For 5, are you telling me you don't know how you want the name sorted ?  In 
that case, perhaps there's no need to retain a sorting field at all.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Keith Medcalf]

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  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

Re: [sqlite] An order by problem, maybe a bug?

[James K. Lowden]

On Fri, 19 Sep 2014 02:02:30 +0100
Simon Slavin  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

Re: [sqlite] An order by problem, maybe a bug?

[FarSight Data Systems]

Thanks, I'll keep it in min.  In this case, howevery, I don't think that will 
be an issue.  All of 
the names are from American published pulp magazines,  writers, artists, and 
editors.

Mark

On Friday, September 19, 2014 02:02:30 AM Simon Slavin wrote:
> On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:
> > that resolved it.  I didn't know you needed to put the desc with both
> > columns.
> > 
> > It means another table I had thought was properly ordered wasn't.
> > 
> > Thank you.
> 
> You're welcome.  Glad you figured it out.
> 
> 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)
> 
> For the second field your name might appear as "Halegua, Mark" and someone
> with a middle name might appear as "Smith, Mark Edward".  The comma is
> needed because some people have a surname which is two separate words, e.g.
> Patrick Nielsen Hayden.  Given the way SQLite works you would want to
> declare the field nameInSortOrder as having COLLATE NOCASE.
> 
> This is especially important if you are storing names which don't all have
> Western-style 'given-name surname' format.  For instance, you may see these
> words between the first and last parts of people's names: "bin", "ben",
> "ibn", "bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not
> middle names.  They mean "son of" or "daughter of" or "from" or other
> things.  They should definitely not be capitalised, except for "O'".  And
> you don't sort on them at all.
> 
> Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted
> together, not as if the name begins with the letters 'MAC'.  It's a
> convention that they all be sorted at the beginning of the 'M' listings,
> ignoring the difference between "Mc" and "Mac".
> 
> For a longer list of reasons, see this article:
> 
>  mes/>
> 
> and for those who like that, there's a similar
> 
> 
> 
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

-- 

Mark S. Halegua
718-360-1712
917-686-8794
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 1:15am, Mark Halegua  wrote:

> that resolved it.  I didn't know you needed to put the desc with both columns.
> 
> It means another table I had thought was properly ordered wasn't.
> 
> Thank you.

You're welcome.  Glad you figured it out.

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)

For the second field your name might appear as "Halegua, Mark" and someone with 
a middle name might appear as "Smith, Mark Edward".  The comma is needed 
because some people have a surname which is two separate words, e.g. Patrick 
Nielsen Hayden.  Given the way SQLite works you would want to declare the field 
nameInSortOrder as having COLLATE NOCASE.

This is especially important if you are storing names which don't all have 
Western-style 'given-name surname' format.  For instance, you may see these 
words between the first and last parts of people's names: "bin", "ben", "ibn", 
"bas", "bat", "O'", "al-", "de" "van de", "Fitz".  They"re not middle names.  
They mean "son of" or "daughter of" or "from" or other things.  They should 
definitely not be capitalised, except for "O'".  And you don't sort on them at 
all.

Similarly, surnames beginning with 'Mac' or 'Mc' should not be sorted together, 
not as if the name begins with the letters 'MAC'.  It's a convention that they 
all be sorted at the beginning of the 'M' listings, ignoring the difference 
between "Mc" and "Mac".

For a longer list of reasons, see this article:



and for those who like that, there's a similar



Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] An order by problem, maybe a bug?

[Mark Halegua]

Simon,
that resolved it.  I didn't know you needed to put the desc with both columns.

It means another table I had thought was properly ordered wasn't.

Thank you.

Mark


On Friday, September 19, 2014 12:50:57 AM Simon Slavin wrote:
> On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:
> > Here are the commands:
> > 
> > select * from contributors order by contrib_lname, contrib_fname; (works
> > properly) select * from contributors order by contrib_lname,
> > contrib_fname desc; (get the same order as above)
> 
> Note that the DESC you provided above applies only to the first name.  If
> you need DESC to apply to the last name too, you want
> 
> select * from contributors order by contrib_lname DESC, contrib_fname desc
> 
> > select * from contributors order by contrib_lname desc; (this works but
> > obviosiosly doesn't sub-order the contrib_fname)
> 
> If the above doesn't solve your problem, please ...
> 
> tell us which version of SQLite you're using.
> 
> give us a couple of INSERT commands which lead to results which are the
> wrong way around.  It should be possible to demonstrate this with just two
> INSERTs.  If it isn't, please try to give us as few as possible rows of
> data to demonstrate the problem.
> 
> If this is a result retrieved by something in one of your programs, please
> try the same commands with one of the SQLite shell tools, downloadable from
> the SQLite site.
> 
> Simon.
> ___
> 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

Re: [sqlite] An order by problem, maybe a bug?

[Simon Slavin]

On 19 Sep 2014, at 12:40am, Mark Halegua  wrote:

> Here are the commands:
> 
> select * from contributors order by contrib_lname, contrib_fname; (works 
> properly)
> select * from contributors order by contrib_lname, contrib_fname desc; (get 
> the same order 
> as above)

Note that the DESC you provided above applies only to the first name.  If you 
need DESC to apply to the last name too, you want

select * from contributors order by contrib_lname DESC, contrib_fname desc

> select * from contributors order by contrib_lname desc; (this works but 
> obviosiosly doesn't 
> sub-order the contrib_fname)

If the above doesn't solve your problem, please ...

tell us which version of SQLite you're using.

give us a couple of INSERT commands which lead to results which are the wrong 
way around.  It should be possible to demonstrate this with just two INSERTs.  
If it isn't, please try to give us as few as possible rows of data to 
demonstrate the problem.

If this is a result retrieved by something in one of your programs, please try 
the same commands with one of the SQLite shell tools, downloadable from the 
SQLite site.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] An order by problem, maybe a bug?

[Mark Halegua]

I've come upon a problem in sqlite3.

Here's the table:

CREATE TABLE contributors(
contrib_id integer primary key, 
contrib_lname char not null, 
contrib_fname char, 
contrib_mname char,  
writer int, 
artist int, 
editor int)

I've inserted several names.  When I order by contrib_lname, contrib_fname I 
get the 
correct order, however when I say desc, I get the same order as without.  It's 
only when I 
don't include the contrib_fname does it come out in last name order, however, 
there are a 
couple of entries with same last names but different first names and they won't 
necessarily 
sort properly if I don't include the contrib_fname column.

Why am I not getting the results I'm expecting?

Here are the commands:

select * from contributors order by contrib_lname, contrib_fname; (works 
properly)
select * from contributors order by contrib_lname, contrib_fname desc; (get the 
same order 
as above)
select * from contributors order by contrib_lname desc; (this works but 
obviosiosly doesn't 
sub-order the contrib_fname)

Mark

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users