Re: PostgreSQL Developer Best Practices

On Tue, Aug 25, 2015 at 12:09 PM, Rob Sargent <robjsargent(at)gmail(dot)com> wrote:

> On 08/25/2015 09: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.
>> No one ever said a number is not natural. just that there is no need to
>> duplicate uniqueness
>> with a separate number.
>>
>> IOW: If we have an account table, then the account_id or account_no
>> would be the primary key. There is no need to have a separate serial
>> id as the primary key.
>>
> If I'm following correctly, you're saying that if the definition of the
> entity contains and arbitrary unique value then use that. Fine. I guess I
> quibble with the notion of VIN as a "natural" attribute of car. (I have no
> firsthand experience with VINs but I would bet there's information tucked
> inside them, which would make me sceptical of using them :) )
>
>
​Yes, the VIN is an encoding in a similar fashion to how Object IDs
function in the computer world:
http://www.zytrax.com/books/ldap/apa/oid.html

The problem with using a VIN is a combination of usability during manual
entry - even with the checksum feature - and the fact that only physically
produced vehicles are assigned one but both manufacturers and their dealers
end up dealing with the concept of a vehicle before one is ever produced.
Neither are overly problematic but they are annoying enough that usually
additional identifiers are constructed an used by the business in order to
avoid having to see the VIN as anything other than an attribute. The
length itself is also problematic - 17 characters typically is a bit much
when the user likely only care about thousands or tens of thousands of
entities at any given time.

David J.