To ID or User_ID, that is the question

So, yeah, the question is:
If I have a table named something like “user”, should I use “id” or “user_id” for the primary key?

I’ve seen it a million times either way. My personal preference is for the former (“id”), because then you get things like:
user.id

instead of:
user.user_id

which feels extremely redundant to me.

Is one better than the other, or is this still a hotly debated topic?

Doesn’t matter one way or the other, as long as the convention is carried consistently among all tables. It’s when you use id on one table and tablename_id on another that confusion reigns.

Personally, I like to use id on the table itself, then on any tables that it is referenced as a FK, I use the table name and Id.

So if I saw a field on the orders table called userId, I would know that field is a foreign key to the user table and ties to user.id.

Okay, glad at least one person uses the same approach as me. =p You described my preferred approach as well (except I usually use underscores instead of camel case =p).

To each their own - I just avoid the extra keystrokes.

But as long as it’s better than one of the dbases we’ve got here - there were 3-4 people building frantically before anyone thought to set standards and we got the full gambit: camelCase, underscores, abbreviations, the id situations defined above - it’s been a lot of fun trying to straighten that out :rolleyes:

Yeah, I’ve been actively working hard to avoid that like the plague. I have everyone looked down to very specific naming conventions for everything. We have a rather small team, so luckily it’s not too hard.

The important thing is to pick convention and stick with it. I have worked with some databases that use one convention and some the other. It really doesn’t matter. At this point in time I prefer id but about year ago I was on the other wagon. When working on things at my day job I try to follow conventions when they exist though what ever they may be even if they make be cringe.

Surprisingly our Oracle DB follows conventions to a T but our MySQL one for all our heavily customized Drupal deployments does not… go figure.

Yeah, I completely agree with the conventions. We’re a small development team within a company (that isn’t actually a software company, but the project we are starting on is probably going to push us in that direction =D). We just got the OK to hire our third developer, so I’m trying to tie up any lose ends for conventions we don’t already have (we already have conventions for just about everything else =)). Luckily, I’m the “boss” of the team, so I get to make these calls and make sure people stick with them. =p