Re: [sqlite] Things you shouldn't assume when you store names

[Richard Damon]

But the main point of the document is that just because you know how
things 'must' be where you are, doesn't mean that every name you need to
handle will be built on those same rules. I can think of cases which
shows problems with most of those rules. The key is that each of the
rules sounds like a rule that someone has assumed, and each of the rules
is something that the author of the article know of (or at least can
think of) a case where that rule doesn't hold. A rule that holds only
99.999% of the time is not always true.

On 11/9/19 9:44 PM, Doug wrote:
> Au Contraire, Jens! In many local contexts you can normalize people's names. 
> I was born in Kansas, USA. My parents filled out a birth certificate for me. 
> It had a place on the form for first name, middle name, last name, and a 
> suffix like II or III.
>
> That birth certificate form determined that everyone born in Kansas (at that 
> time), had a first, middle, and last name. There was no discussion of the 
> matter. That's the way it was. The form led the way; people never thought 
> about whether it was effective or not. Each newly-born child was given a 
> first, middle, and last name.
>
> Effective was irrelevant for that system. There was no option, no 
> alternative. It simply was.
>
> All systems are like that at each moment in time. They are what they are at 
> any moment in time, and they force the users to behave the way the system 
> wants them to behave. If you want to change the system and momentum is on 
> your side, then immediately you have a new system - at that moment in time. 
> It is composed of the old system and the momentum.
>
> Back to names: just like the birth certificate, a system which assigns a name 
> to you, actually coerces you to have that name, because within that system, 
> you exist as that name. The "names" article is totally wrong when it says 
> that each assumption is wrong. Each of those assumptions is correct, and I 
> can find at least one system which makes each one correct. Within each 
> system, the assumption works, and is valid.
>
> My two cents...
> Doug 
>
>> -Original Message-
>> From: sqlite-users 
>> On Behalf Of Jens Alfke
>> Sent: Saturday, November 09, 2019 5:11 PM
>> To: SQLite mailing list 
>> Subject: Re: [sqlite] Things you shouldn't assume when you store
>> names
>>
>> On Nov 9, 2019, at 1:09 PM, sky5w...@gmail.com wrote:
>>> In this case, data modelers hoping to save a column. arrggg.
>>> It flies in the face of data normalization and pushes the
>> problem down the
>>> line.
>> But you _cannot_ normalize people’s names; that’s the exact point
>> of that article. Anything you assume about the structure of a name
>> will be wrong in some culture.
>>
>> -Jens
>> ___
>> sqlite-users mailing list
>> sqlite-users@mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
>> users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


-- 
Richard Damon

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

Re: [sqlite] Things you shouldn't assume when you store names

[Doug]

Au Contraire, Jens! In many local contexts you can normalize people's names. I 
was born in Kansas, USA. My parents filled out a birth certificate for me. It 
had a place on the form for first name, middle name, last name, and a suffix 
like II or III.

That birth certificate form determined that everyone born in Kansas (at that 
time), had a first, middle, and last name. There was no discussion of the 
matter. That's the way it was. The form led the way; people never thought about 
whether it was effective or not. Each newly-born child was given a first, 
middle, and last name.

Effective was irrelevant for that system. There was no option, no alternative. 
It simply was.

All systems are like that at each moment in time. They are what they are at any 
moment in time, and they force the users to behave the way the system wants 
them to behave. If you want to change the system and momentum is on your side, 
then immediately you have a new system - at that moment in time. It is composed 
of the old system and the momentum.

Back to names: just like the birth certificate, a system which assigns a name 
to you, actually coerces you to have that name, because within that system, you 
exist as that name. The "names" article is totally wrong when it says that each 
assumption is wrong. Each of those assumptions is correct, and I can find at 
least one system which makes each one correct. Within each system, the 
assumption works, and is valid.

My two cents...
Doug 

> -Original Message-
> From: sqlite-users 
> On Behalf Of Jens Alfke
> Sent: Saturday, November 09, 2019 5:11 PM
> To: SQLite mailing list 
> Subject: Re: [sqlite] Things you shouldn't assume when you store
> names
> 
> On Nov 9, 2019, at 1:09 PM, sky5w...@gmail.com wrote:
> >
> > In this case, data modelers hoping to save a column. arrggg.
> > It flies in the face of data normalization and pushes the
> problem down the
> > line.
> 
> But you _cannot_ normalize people’s names; that’s the exact point
> of that article. Anything you assume about the structure of a name
> will be wrong in some culture.
> 
> -Jens
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-
> users

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

Re: [sqlite] Things you shouldn't assume when you store names

[Jens Alfke]

On Nov 9, 2019, at 1:09 PM, sky5w...@gmail.com wrote:
> 
> In this case, data modelers hoping to save a column. arrggg.
> It flies in the face of data normalization and pushes the problem down the
> line.

But you _cannot_ normalize people’s names; that’s the exact point of that 
article. Anything you assume about the structure of a name will be wrong in 
some culture.

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

Re: [sqlite] Things you shouldn't assume when you store names

[Warren Young]

On Nov 9, 2019, at 12:25 PM, Simon Slavin  wrote:
> 
> Every time I saw a field called 'firstname' or 'second name' or 'surname' or 
> 'familyname' I groaned.

I just had a fight with my insurance company who had me sign up for their new 
web portal, which only asked for first and last name, but it kept telling me I 
wasn’t a customer.  They’d been happily accepting my credit card payments for 
years, but I’m not a customer?!

We eventually figured out what went wrong: on the paper sign-up form, they 
demanded my full legal name, which has a suffix. There was no spot on the form 
for my name’s suffix, so I put it after the last name, and their data entry 
drone put it in the database’s last-name field as “Young II”, so to their DBMS, 
there was indeed no “Warren”, “Young” row!
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Things you shouldn't assume when you store names

[sky5walk]

Ok, I'll bite.
The 'current consensus' in any system is tenuous and not an arbiter of its
effectiveness.
In this case, data modelers hoping to save a column. arrggg.
It flies in the face of data normalization and pushes the problem down the
line.
Forgive my simple linear thinking on the immensely complex topic of 'your
name here and here and here'.
Sincerely,
alias  ;)

On Sat, Nov 9, 2019 at 2:26 PM Simon Slavin  wrote:

> Since I don't see many posts yet this weekend, please excuse one of mine
> which isn't exactly on charter.  Feel free to argue me out of posting in
> personal (offlist) email.
>
> In a previous job I got to see databases made up by all sorts of other
> people and organisations.  Every time I saw a field called 'firstname' or
> 'second name' or 'surname' or 'familyname' I groaned.  So I was nodding
> along as I read this:
>
> <
> https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-believe-about-names/
> >
>
> I think this one is unusually well-written.
>
> In case you want to know how best to handle personal names, the current
> consensus seems to be to use a single field containing the whole name,
> which can be searched by substring.  Computer systems for places with
> non-Roman character sets sometimes use two fields: name in local characters
> (Chinese, Devanagari, etc.) and name in Roman characters.
>
> Also note that current privacy legislation in the US and EU means you are
> not allowed to ask for anything like 'full legal name' unless you cannot
> run your business without it.  Ask them for their name, and store what they
> tell you, with the words in the order they gave them.  If you need to sort
> people in name order (think very hard about why, first), create a field
> called 'sort order' and populate it yourself.  Sorting is your problem, not
> that of the people you're sorting.
>
> Part of a continuing series including falsehoods about dates, times,
> places, street addresses, gender, relations, phone numbers, taxes, and
> amounts of money.
>
> Good luck, and watch your back.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Things you shouldn't assume when you store names

[Simon Slavin]

Since I don't see many posts yet this weekend, please excuse one of mine which 
isn't exactly on charter.  Feel free to argue me out of posting in personal 
(offlist) email.

In a previous job I got to see databases made up by all sorts of other people 
and organisations.  Every time I saw a field called 'firstname' or 'second 
name' or 'surname' or 'familyname' I groaned.  So I was nodding along as I read 
this:



I think this one is unusually well-written.

In case you want to know how best to handle personal names, the current 
consensus seems to be to use a single field containing the whole name, which 
can be searched by substring.  Computer systems for places with non-Roman 
character sets sometimes use two fields: name in local characters (Chinese, 
Devanagari, etc.) and name in Roman characters.

Also note that current privacy legislation in the US and EU means you are not 
allowed to ask for anything like 'full legal name' unless you cannot run your 
business without it.  Ask them for their name, and store what they tell you, 
with the words in the order they gave them.  If you need to sort people in name 
order (think very hard about why, first), create a field called 'sort order' 
and populate it yourself.  Sorting is your problem, not that of the people 
you're sorting.

Part of a continuing series including falsehoods about dates, times, places, 
street addresses, gender, relations, phone numbers, taxes, and amounts of money.

Good luck, and watch your back.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQLITE_DETERMINISTIC and custom function optimization

[Mario M. Westphal]

Thanks to all the friendly people who commented on my question. Much
appreciated :-)

 

I was able to solve this with a small trick:

 

I created a small 'state' struct with a rowid and the result (float) for
that row.

Using the "user data" parameter when creating EXPENSIVE_FUNCTION, I supply a
pointer to this struct to the function.

(I have full control over when EXPENSIVE_FUNCTION is created, dropped and
used. This trick may not work in other use cases.)

 

Then I've changed the EXPENSIVE_FUNCTION signature to also take the rowed as
the first parameter: 

 

EXPENSIVE_FUNCTION(rowid,?99,vdata) 

 

EXPENSIVE_FUNCTION uses sqlite3_user_data() to get the state struct pointer
and then compares the rowid parameter with the rowid in the struct.

If they are identical, the cached result is used. Very fast.

Else the result for the requested row is calculated and cached. This is the
slow part.

 

Thanks to this change, EXPENSIVE_FUNCTION needs to perform the slow
calculations in only 46,031 of 91,806 calls. 

In all other cases the cached value from the previous call can be used.

The runtime drops to 2,580ms (from 3,350ms) for the 45K rows sample set.
Which yields a roughly 20% better runtime. Very good.

 

I'm always amazed about what can be achieved with SQLite. Very impressive
product and API design.

 

Thank again for all who provided suggestions and commented.

 

-- Mario

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