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
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.
