Re: ID column naming convention

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