←back to thread

The principles of database design, or, the Truth is out there

(ebellani.github.io)
121 points b-man | 8 comments | 19 May 25 02:58 UTC | HN request time: 0.001s | source | bottom
Show context
mrkeen ◴[19 May 25 04:41 UTC] No.44026549[source]▶
> Principle of Essential Denotation (PED): A relation should be identified by a natural key that reflects the entity’s essential, domain-defined identity — not by arbitrary or surrogate values.

  create table citizen (
    national_id national_id primary key,
    full_name text);
Is national_id really a natural key, or is it someone else's synthetic key? If so, should the owner of that database have opted for a natural key rather than a synthetic key?

More arguments for synthetic over natural keys: https://blog.ploeh.dk/2024/06/03/youll-regret-using-natural-...

replies(3): >>44026597 #>>44026611 #>>44027242 #
1. tuatoru ◴[19 May 25 04:53 UTC] No.44026597[source]▶
The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth. Your birth certificate is your primary identification document.

However that still runs into problems of nondurability of the key in cultures that delay naming for a few years. To name one problem.

So yeah, use a big enough integer as your key, and have appropriate checks on groups of attributes like this.

However, if you are only interested in citizens, then a "natural" key is the citizen id issued by the government department responsible for doing that. (Citizen is a role played by a natural person so probably doesn't have too many attributes.) I still wouldn't use that as a primary key on the citizen table, though.

replies(6): >>44026790 #>>44027013 #>>44027124 #>>44027728 #>>44036602 #>>44052536 #
2. bouke ◴[19 May 25 05:49 UTC] No.44026790[source]▶
That natural key isn’t guaranteed to be unique.
replies(1): >>44036605 #
3. stevoski ◴[19 May 25 06:35 UTC] No.44027013[source]▶
I know someone who doesn’t know when she was born, nor who her mother is.

She doesn’t have a birth certificate.

She was born in a country that was enduring several years of brutal war.

I know another person whose national ID was changed. Systems that use national ID as primary key failed to accept this change.

4. anon7000 ◴[19 May 25 06:57 UTC] No.44027124[source]▶
https://www.kalzumeus.com/2010/06/17/falsehoods-programmers-...
5. globular-toast ◴[19 May 25 08:47 UTC] No.44027728[source]▶
> The "natural key" for a (natural) person is compound: full name and mother's full name, plus date, time and place of birth.

All it would take for a Bobby Tables[0] moment is a mother to have twins delivered by caesarean and given the same name... The same name thing has already been done[1].

[0] https://xkcd.com/327/

[1] https://www.walesonline.co.uk/news/real-life/mum-gives-twin-...

6. b-man ◴[20 May 25 00:45 UTC] No.44036602[source]▶
> I still wouldn't use that as a primary key on the citizen table, though.

Why not?

7. b-man ◴[20 May 25 00:46 UTC] No.44036605[source]▶
Depends on the universe of discourse adopted.
8. HelloNurse ◴[21 May 25 15:28 UTC] No.44052536[source]▶
We need a new term of art: "naive key".