Re: PostgreSQL Developer Best Practices

On 08/25/2015 05:28 PM, Adrian Klaver wrote:
> On 08/25/2015 05:17 PM, Melvin Davidson wrote:
>> I think a lot of people here are missing the point. I was trying to give
>> examples of natural keys, but a lot of people are taking great delight
>> in pointing out exceptions to examples, rather than understanding the
>> point.
>> So for the sake of argument, a natural key is something that in itself
>> is unique and the possibility of a duplicate does not exist.

Correct.

>> Before ANYONE continues to insist that a serial id column is good,
>> consider the case where the number of tuples will exceed a bigint.
>> Don't say it cannot happen, because it can.

Yes it can.

>> However, if you have an alphanumeric field, let's say varchar 50, and
>> it's guaranteed that it will never have a duplicate, then THAT is a
>> natural primary

Wrong. Refer back to your above definition. It is definitely possible,
based on a varchar(50) that a duplicate will happen. A better definition
would be something along the lines of:

A natural key is distinct and is derived from the data being stored.

>
> That is a big IF and a guarantee I would not put money on.

Right, here is a perfect example. Generally speaking if you are storing
a United States company's information, a natural primary key could be an
FEIN. However, there is an exception that would have to be incorporated
into that idea. If the company is a Sole Proprietorship the FEIN may
actually be the SSN of the owner, but not necessarily. Then you have to
ask yourself if that matters. It may not depending on the application
you are building or the reason the data is being stored.

>
>> key and beats the hell out of a generic "id" field.
>>
>> Further to the point, since I started this thread, I am holding to it
>> and will not discuss "natural primary keys" any further.

That doesn't mean others won't.

JD

--
Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564
PostgreSQL Centered full stack support, consulting and development.
Announcing "I'm offended" is basically telling the world you can't
control your own emotions, so everyone else should do it for you.