Re: ID column naming convention

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.
--
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