Re: PostgreSQL Developer Best Practices

On 26/08/15 08:56, Adrian Klaver wrote:
> On 08/25/2015 08:40 AM, Melvin Davidson wrote:
>> Adrian,
>>
>> Stop being so technical. When we/I speak of natural keys, we are talking
>> about the column
>> that would NATURALly lend itself as the primary key.
>
> Pretty sure this is a technical list:)
>
Don't let inconvenient facts get in the way of a good argument!  :-)

[...]
> Pretty sure parts are not unique to an exact vehicle, unless you are
> talking a totally handmade one. They are not even unique to make and
> model. As an example, I used to work on Class B Isuzu trucks. These
> models(FTR) where also built for Chevrolet as the Forward models. So
> right of the bat there where two part numbers for each part, one that
> started with 9 if you got it from Chevrolet and one with 11 from
> Isuzu, if memory serves. Then Isuzu decided to reorganize their part
> numbers, so that introduced another number, all pointing to the exact
> same part. Then there where those parts available from the parts
> houses(NAPA, etc).
>
> Then there was the greenhouse I worked for where we supplied UPC coded
> tags for our customers. In the beginning, it was simple, the item
> portion of the UPC was unique and with the company prefix served as a
> 'natural' key for the tags. Then the chain stores we worked with must
> have all gone to the same seminar on how to be Walmart and decided
> they did not want unique numbers, but UPCs tied to price groups that
> covered a variety of plants. Luckily, I was too stupid to
Natural Stupidity??? :-)

(Sorry, couldn't resist!)

> know surrogate keys where bad and had a sequence attached to the tag
> table. This then became the tag id and made life a lot easier during
> the transition. It still remains there, because people are people and
> 'natural' tends to be artificial and transient.
Extremely good examples, I'll bear them in mind - makes me even more
keen on surrogate primary keys.  I'm always very wary when people tell
me some numbering scheme will NEVER change!!!

[...]


Cheers,
Gavin