Hacker News new | ask | show | jobs
by sbov 2979 days ago
> Similarly using Person.PersonID instead of Person.ID gives consistency in diagrams and foreign key naming.

I assume you mean you just use PersonID as the foreign key. This oftentimes introduces ambiguity into what the relationship actually is. I prefer names that describe the actual relationship (e.g. author, owner, approver, etc) rather than letting other people guess what it is.
2 comments
always_good 2979 days ago
I usually want to reserve the noun like "author" as the embedded record after a join. That way "author_id" is always the key and then "author" is the json_agg joined object that embeds the whole record.

Otherwise you're actually introducing ambiguity imo.

link
zo1 2979 days ago
I think he's referring to using "[Table]ID" as the primary key on [Table]?

So now you have to join with the Person table on Person.PersonID from your local column PersonID. I much prefer the other way around: Table.ID with foreign keys being "TableID".

link
slobotron 2979 days ago
I find "[Table]ID" to be bit more readable in joins. I.e.

    SELECT * FROM table1 INNER JOIN table2 USING (table1_id);
vs

    SELECT * FROM table1 INNER JOIN table2 ON (table2.table1_id = table1.id);
link