Re: PostgreSQL Developer Best Practices

On 25/08/15 19:04, Karsten Hilbert wrote:
> On Tue, Aug 25, 2015 at 02:02:17PM +1200, Gavin Flower wrote:
>
>> On 25/08/15 01:15, Ray Cote wrote:
>>> On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net
>>> <mailto:Karsten.Hilbert@gmx.net>> 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.
> Gavin, Ray,
>
> I certainly didn't write any of the above.
>
> Karsten
Hi Karsten,

It took me a couple of minutes, but I traced "9.  ..." to
melvin6925@gmail.com who opened the thread

Looks like Ray misquoted back in the entry that can be identified by
(using the 'source' option on my mail client)

    From: Ray Cote <rgacote@appropriatesolutions.com>
    Date: Mon, 24 Aug 2015 09:15:27 -0400
    Message-ID:
    <CAG5tnzqTausEhFtRpfWCunx4YNFuGTFyUZyTkn5f2E7RaYKE=g@mail.gmail.com>

which was

    On Sat, Aug 22, 2015 at 11:46 AM, Karsten Hilbert <Karsten.Hilbert@gmx.net>
    wrote:

    > > 1. Prefix ALL literals with an Escape
    > >    EG:  SELECT E'This is a \'quoted literal \'';
    > >         SELECT E'This is an unquoted literal';
    > >
    > >    Doing so will prevent the annoying "WARNING:  nonstandard use of
    > escape in a string literal"
    >

    I'd be concerned that what is missing here is the bigger issue of  Best
    Practice #0: Use Bound Variables.
    The only way I've seen invalid literals show up in SQL queries is through
    the dynamic generation of SQL Statements vs. using bound variables.
    Not using bound variables is your doorway to SQL injection exploits.


    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?
    3) Your example shows the id as a bigint, but your rule is not limited to
    integers.
    What if your table is country populations and the primary key is country
    name?
    Now, you have quite large foreign keys (and a country changing its name is
    not unheard of).
    (and let's not even get started on case-sensitivity or character encodings).


Cheers,
Gavin