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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
