Re: PostgreSQL Developer Best Practices

On Mon, Aug 24, 2015 at 10:02 PM, Gavin Flower <
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 AS parent_id, child.id AS child_id, [...]
FROM parent
JOIN child ON (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.