Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it better to name the primary key column id or *_id?

Tags:

sql

I've been using Rails for a few years and I've grown used to the convention of naming the primary key column id. But I've run across lots of examples in SQL books that name the primary key column something like employee_id for an employees table or feed_id for a feeds table.

One advantage of the 2nd system seems to be that you can use USING() more to produce more concise SQL queries:

select feeds.title, items.title from items inner join feeds USING(feed_id);

As opposed to

select feeds.title, items.title from items inner join feeds on feeds.id = items.feed_id;

Which naming convention is better? Which is favored by experienced database administrators?

Also, is it better to pluralize the name of the table?

like image 796
dan Avatar asked Jun 24 '11 15:06

dan


1 Answers

I always use the verbose form (i.e. 'employee_id' rather than 'id') as it is more descriptive. If you are joining more than one table and both have 'id' column you will have to alias 'id' if you need to SELECT both of the ids. Also, as you mentioned, you get the advantage of USING clause. In the grand scheme of things it isn't a huge factor one way or the other but the more verbose form gives you advantages.

like image 139
Brady Holt Avatar answered Nov 15 '22 05:11

Brady Holt