On 08/25/2015 02:44 PM, Gavin Flower wrote:
On 26/08/15 02:17, Adrian Klaver wrote:
[...]
2) One of the older unique natural keys (genus, species) is not so
unique. I am a fisheries biologist by training and in my time the
'unique' identifier for various fishes has changed. Now that
ichthyologists have discovered DNA testing, it can be expected there
will be even more changes. This is even more apparent when you go back
in in history. As an example:
https://en.wikipedia.org/wiki/Rainbow_trout
Rainbow trout
Current
Oncorhynchus mykiss
Past
Salmo mykiss Walbaum, 1792
Parasalmo mykiss (Walbaum, 1792)
[...]
Salmo gilberti Jordan, 1894
Salmo nelsoni Evermann, 1908
So you probably need a date stamp so you could record things relating to
the correct name for a given period in a mapping table, and still relate
to the same surrogate key for referencing other tables.
Maybe even worse, is when a species is suddenly found to be 2 or more
distinct species!
Funny you should say that. Furry critters instead of slimy:
http://news.nationalgeographic.com/2015/07/150730-jackals-wolves-evolution-new-species-animals-africa/
Something similar could happen with account numbers: 2 companies with
similar names might be assigned to the same account number, and lots of
transactions recorded before the mistake is discovered. Though obviously
a surrogate key would not give you complete protection from a lot of
work sorting the mess out, but it would probably help!
Or if you have a mortgage with Well Fargo and find your account number
is being used in their agent training program which explains why you
have been receiving all sorts of correspondence saying your account is
in arrears and is facing foreclosure(personal experience).
Bottom line is databases are great and theory is useful, but it all goes
out the window when people start meddling.
I read on post a year or 2 back, a guy in Europe had at least 4
different variations on his name depending on the country he was in and
the local language and cultural norms.
I am familiar with that issue.
When I worked at a freezing works in the 1970's in Auckland, I heard
that the pay roll allowed for over 52 different names per employee (per
year?). Though, I was never told the maximum name changes ever used.
Essentially management might fire someone, but the union would complain,
and they would be rehired under a different name - so I was told! So
the correct holiday pay & PAYE tax deductions would still relate to the
same individual no matter how many name changes they had.
Or a system I took over where someone had made a natural primary key of
first name, last name and that was all. So you had John Smith, John
Smith2, etc. Poor design obviously, but that stuff is out there.
Cheers,
Gavin
--
Adrian Klaver
adrian.kla...@aklaver.com
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
