On 10/15/15 8:28 PM, Gavin Flower wrote:
It would seem to be very dodgy to us a join based on apparently very different semantic implied by 'blah.person_id = foo.invoice_id'!!! :-) Because 2 fields in different tables have the same name, it does not necessarily mean they have the same semantics. For example 2 tables could have a field named 'start_date', but the one in a table called 'employment' would have different semantics to the one in 'project'.
Right, which is why the fields should be called employment_start_date and project_start_date, not just start_date.
Since 'id' is only used to indicate a PRIMARY KEY, there is less confusion in joins, and it is clear when something is a foreign key rather than a PRIMARY KEY. For example, if two tables both refer to the same human, you can join using a.human_id = b.human_id - and it is clearer when you are joining a child to a parent table, for example line_item.stock_id = stock.id. Adopting you convention, it would result in not only picking up foreign key references, but also the primary keys - which may, or may not, too helpful!
It generally shouldn't matter, because a person_id is *always* a person_id. In cases where it does matter then the field name alone probably won't help you much, if at all.
Of course, this has been a debate forever, so I know neither of us will convince the other to change. ;) I just wanted to point out some things that hadn't come up already.
BTW, I found Karsten's idea of using 'pk' for the surrogate key, and fk_table_name interesting. It helps avoid ambiguity from externally generated ID values.
-- Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX Experts in Analytics, Data Architecture and PostgreSQL Data in Trouble? Get it in Treble! http://BlueTreble.com -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
