Re: ID column naming convention - Mailing list pgsql-general

On 16/10/15 13:09, Jim Nasby wrote:
> On 10/13/15 2:34 PM, Gavin Flower wrote:
>>>
>> My practice is to name the PRIMARY KEY as id, and foreign keys with the
>> original table name plus the sufiix_id.
>>
>> By leaving the table name off the primary key name, and just using id,
>> makes it more obvious that it is a primary key (plus it seems redundant
>> to prefix the primary key name with its own table name!).
>
> There's two things that are ugly about that though:
>
> Joins become MUCH easier to screw up. When you have 5 different fields
> that are all called 'id' it's trivial to mix them up. It's much harder
> to accidentally do something like 'blah.person_id = foo.invoice_id'.
>
> The other issue is common to all "bare word" names (id, name,
> description, etc): it becomes completely impossible to find all
> occurrences of something in code. If you grep your entire codebase for
> 'person_id', you know you'll find exactly what you want. Grepping for
> 'id' OTOH would be useless.
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'.

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 would be very rare to have a join such as project.id = task.id, it is
usually a mistake to join tables on their primary key - so using just
'id' as the PRIMARY KEY name is a bonus.

I once devised a stored procedure in SyBase with over 3,000 lines of SQL
(I would have broken it up in smaller units, but it was not practicable
in that development environment).  It had 7 temporary tables, 5 used
'id' as the PRIMARY KEY - and 2 used the name of the PRIMARY KEY of an
existing table ('tcs_id' & 'perorg_seq'), because that made more sense,
as they had the the same semantic meaning. I did not design the 2
databases I queried, but I suspect sometimes I might decide it best to
use something other than just 'id' - but it would be very rare (I won't
say never!) that I'd use the table name as a prefix for the primary key.

Searching on a bare word names can be useful when the fields have
similar, related semantics.  In a real database, I'd be very unlikely to
use 'name' for a field, though using 'description' might be valid.
Though in general, I would agree that using several words in a name is
normally preferable. Also it would also be better to define appropriate
DOMAINs rather than just using bare types like 'text' & 'int' - to
better document the semantics and make it easier to change things in a
more controlled way.

If one was grepping for the occurrences of the use of the PRIMARY KEY of
the table human, you would look for 'human_id' you would only grep for
'id' if one wanted to find the use of PRIMARY KEYs.

No naming convention is perfect in all situations, and I'll adapt mine
as appropriate.  In my experience, my convention (well to be honest, I
adopted it from others - so I can't claim to have originated it!) seems
to be better in general.

Essentially it is a guideline, I won't insist that you have have your
computers confiscated  if you use a different convention!