Re: PostgreSQL Developer Best Practices

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!

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!

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.

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.

Cheers,
Gavin