Natural primary keys, Was: Cayenne with JSF 2

[Aristedes Maniatis <ar...@maniatis.org>]

On 4/04/12 7:06 PM, Durchholz, Joachim wrote:
>> However any time you have a primary key with meaning is probably
>> a mistake in database design. You are best trying to avoid this
>> at all costs unless you are constrained by a legacy system in
>> some way.
>
> This is common advice, but the topic is controversial.
> Even in non-legacy databases, having natural PKs can have advantages:

[snip]

I take your points, but almost anything that looks like a natural key... rarely is. Back when I developed systems for other people, I could not count the number of times a customer guaranteed that a certain value would never ever change. Ever. And of course it did.

People who aren't programmers have a more relativistic concept of "never". To them, getting hit by lighting is "something that just never happens". If it happens once every 5 years, that might also qualify as "never". To them, your question simply means "so rare as to be not important to their daily lives". But of course there is a situation when that EAN isn't unique. When an ISBN is accidentally used twice for the casebound and software editions (they should be different). For a programmer unique and immutable have a special absolute meaning.

Yes a government tax number or social security number is unique and immutable. And that would be true only if your database table was a table representing social security numbers. Instead it probably represents 'people' and then the correlation becomes far less certain. Do government departments ever make mistakes? Do they reassign numbers to people in a witness protection program?


Maybe there are exceptions. I can't think of any. At any rate, once you give yourself over to an ORM (Cayenne or anything else), the agreement you make with the software is "Give me objects and don't bother me with how they are stored." SQL with a primary key or 12 chimpanzees with punch cards, it doesn't matter. Sure you can open the hood and tinker with the engine. But if you just want to just drive the car, primary keys are not something that should be exposed in your Java code.


Ari



-- 
-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

Re: Natural primary keys, Was: Cayenne with JSF 2

[Mike Kienenberger <mk...@gmail.com>]

We're not talking about whether or not a SSN uniquely identifies a person.

We are talking about the process for changing up an
incorrectly-entered natural primary key, for whatever reason that
might be.

On Wed, Apr 4, 2012 at 9:36 AM, Durchholz, Joachim
<Jo...@hennig-fahrzeugteile.de> wrote:
> Seems like everybody is looking at the institutions that can't keep their numbers constant.
>
> Give it a break.
> Ordinal numbers of chemical elements cannot change, by definition.
> I may be too optimistic about ISBNs and EANs. Haven't dealt with them and don't know how reliable their issuers are. I guess if they are using their own numbers in a PK in any table that's moderately important, they should catch any duplicates before they make it into the wild. SSNs are issued decentrally, so I'm not surprised they aren't unique; ID cards are issued centrally and managed centrally, so the risk of getting caught in a goof-up is considerably smaller.
>
> Oh, and if you're a company caught in the SSN goof-up, using a synthetic key wouldn't have helped in the least. There is no better ID available when it comes to US citizens, so if you're using data from external sources, your data will be goofed up anyway.
> Things are different if you never use the SSN in external communication to other databases. However, even then you'll never know whether the persons with the same SSN are the same (just changed name and location) or not; there's no better way to identify US citizens, and a synthetic PK won't help you much to resolve that kind of issue!

RE: Natural primary keys, Was: Cayenne with JSF 2

["Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>]

Seems like everybody is looking at the institutions that can't keep their numbers constant.

Give it a break.
Ordinal numbers of chemical elements cannot change, by definition.
I may be too optimistic about ISBNs and EANs. Haven't dealt with them and don't know how reliable their issuers are. I guess if they are using their own numbers in a PK in any table that's moderately important, they should catch any duplicates before they make it into the wild. SSNs are issued decentrally, so I'm not surprised they aren't unique; ID cards are issued centrally and managed centrally, so the risk of getting caught in a goof-up is considerably smaller.

Oh, and if you're a company caught in the SSN goof-up, using a synthetic key wouldn't have helped in the least. There is no better ID available when it comes to US citizens, so if you're using data from external sources, your data will be goofed up anyway.
Things are different if you never use the SSN in external communication to other databases. However, even then you'll never know whether the persons with the same SSN are the same (just changed name and location) or not; there's no better way to identify US citizens, and a synthetic PK won't help you much to resolve that kind of issue!

Re: Natural primary keys, Was: Cayenne with JSF 2

[Michael Gentry <mg...@masslight.net>]

Maybe your government doesn't goof up social security numbers, but
ours does.  :-)

http://www2.nbc4i.com/news/2010/dec/06/2/study-finds-millions-duplicate-social-security-num-ar-316988/

Also, don't forget that if a social security number (let's assume the
government never messes up and assigns the same number to multiple
people) is entered by hand and is used as a natural primary key, there
is a non-zero chance that the person entering the number will mistype
it and you've just messed up your keys and have a mess to clean up.

mrg


On Wed, Apr 4, 2012 at 5:32 AM, Aristedes Maniatis <ar...@maniatis.org> wrote:
> Yes a government tax number or social security number is unique and
> immutable. And that would be true only if your database table was a table
> representing social security numbers. Instead it probably represents
> 'people' and then the correlation becomes far less certain. Do government
> departments ever make mistakes? Do they reassign numbers to people in a
> witness protection program?