Re: PostgreSQL Developer Best Practices

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(dot)klaver(at)aklaver(dot)com