Re: PostgreSQL Developer Best Practices

From: Gavin Flower <GavinFlower(at)archidevsys(dot)co(dot)nz>
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>, Rob Sargent <robjsargent(at)gmail(dot)com>, Melvin Davidson <melvin6925(at)gmail(dot)com>, pgsql-general General <pgsql-general(at)postgresql(dot)org>
Subject: Re: PostgreSQL Developer Best Practices
Date: 2015-08-25 21:44:54
Message-ID: 55DCE1D6.1080907@archidevsys.co.nz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Adrian Klaver 2015-08-25 21:48:33 Re: PostgreSQL Developer Best Practices
Previous Message Gavin Flower 2015-08-25 21:23:13 Re: PostgreSQL Developer Best Practices