Re: PostgreSQL Developer Best Practices

On 25/08/15 14:45, David G. Johnston wrote:
> On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower
> <GavinFlower@archidevsys.co.nz
> <mailto:GavinFlower@archidevsys.co.nz>>wrote:
>
>     Also the best practice is to make the primary key name 'id' as you
>     do know the table it is in, so prepending the table name is
>     redundant - so you can clearly identify foreign keys because the
>     suffix '_id 'is prepended by the table name of the referenced
>     table.  Hence 'id' is a primary key, and account_id is a foreign
>     key pointing into the account table.
>
>
> ​ I would much rather be able to write:
>
> SELECT parent_id, child_id, [...]
> FROM parent
> JOIN child USING (parent_id)
>
> instead of
>
> SELECT parent.id <http://parent.id> AS parent_id, child.id
> <http://child.id> AS child_id, [...]
> FROM parent
> JOIN child ON (parent.id <http://parent.id> = child.parent_id)
>
> ​ Yes, looking at the parent table it is obvious that the id you are
> looking at is the "parent" id.  But as soon as you join two or more
> tables you are guaranteed to have multiple columns with the name "id"
> that you now need to disambiguate.
>
>
> The column name "table_id" refers to the primary identifier for that
> entity no matter where it appears.  I'd rather have one redundant
> situation than one exception to the rule.
>
> David J.
>
Hmm...

I consider it good practice to always give an alias for each table used,
especially for non trivial SQL statements.

So I think the above would look better (using slightly more realistic
table names) as:

SELECT
     c.id,
     s.id,
     [...]
FROM
     company c
     JOIN shop s USING (s.company_id = c.id);

Which is I think a lot clearer (it is obvious that you are joining a
foreign key with a primary key), and you can add more stuff without it
suddenly becoming ambiguous.

I once wrote a Sybase stored proc with over 3000 lines of SQL (not
practical to split it up, unfortunately), individual selects were often
over half a page.  It interrogated 17 tables from two different
databases and needed 5 temporary tables.




Cheers,
Gavin

P.S.  the use of '[...]' was started by me way back in the heyday of
usenet, in the beginning of the 1990's!  Previously people used '[
omitted ]'.