Re: PostgreSQL Developer Best Practices

On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote
<rgacote(at)appropriatesolutions(dot)com> wrote:

>> 9. Do NOT arbitrarily assign an "id" column to a table as a primary key
>> when other columns
>> are perfectly suited as a unique primary key.
>> ... Good example:
>> CREATE TABLE accounts
>> ( accout_id bigint NOT NULL ,
>
> I would not consider the general use of natural primary keys to be best
> practice.Let's assume your account_id field is used as a foreign key in
> a dozen other tables.
> 1) What happens if someone mis-types the account-id?
> To correct that, you also need to correct the FK field in the other
> dozen tables.
> 2) What happens when your company starts a new project (or buys a
> competitor) and all the new account numbers are alpha->numeric?

Point 9 is well-intentioned, but perhaps needs to be clarified/rephrased:
Developers should not be creating production-grade tables devoid of
well-defined business keys, period. That would be regardless of whether
they're used as de facto primary keys or simply as unique keys.

As long as that is made clear as a foundational requirement, then
developers should be allowed some leeway as to the subsequent design
choice between synthetic vs natural keys. Further to the above remarks,
offering some guidelines on the trade-offs would be beneficial. E.g., if
natural keys are chosen as Primary, it's likely that cascading mechanisms
ought to be implemented. Conversely, if synthetic keys are chosen as
Primary, they must be accompanied by a legitimate Unique natural key.

- John