Re: PostgreSQL Developer Best Practices - Mailing list pgsql-general

From John Turner
Subject Re: PostgreSQL Developer Best Practices
Date
Msg-id op.x3vnwellk4admm@eis158.energi.com
Whole thread Raw
In response to Re: PostgreSQL Developer Best Practices  (Ray Cote <rgacote@appropriatesolutions.com>)
Responses Re: PostgreSQL Developer Best Practices
Re: PostgreSQL Developer Best Practices
List pgsql-general
On Mon, 24 Aug 2015 09:15:27 -0400, Ray Cote <rgacote@appropriatesolutions.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

pgsql-general by date:

Previous
From: "David G. Johnston"
Date:
Subject: Re: PostgreSQL Developer Best Practices
Next
From: "Joshua D. Drake"
Date:
Subject: Re: PostgreSQL Developer Best Practices