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(at)archidevsys(dot)co(dot)nz
> <mailto:GavinFlower(at)archidevsys(dot)co(dot)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 ]'.