Database design - primary key naming conventions

I am interested to know what people think about (AND WHY) the following 3 different conventions for naming database table primary keys in MySQL?

-Example 1-

Table name: User,
Primary key column name: user_id

-Example 2-

Table name: User,
Primary key column name: id

-Example 3-

Table name: User,
Primary key column name: pk_user_id

Just want to hear ideas and perhaps learn something in the process :)

Thanks.

Solution 1:

I would go with option 2. To me, "id" itself seems sufficient enough. Since the table is User so the column "id" within "user" indicates that it is the identification criteria for User.

However, i must add that naming conventions are all about consistency. There is usually no right / wrong as long as there is a consistent pattern and it is applied across the application, thats probably the more important factor in how effective the naming conventions will be and how far they go towards making the application easier to understand and hence maintain.

Solution 2:

I always prefer the option in example 1, in which the table name is (redundantly) used in the column name. This is because I prefer to see ON user.user_id = history.user_id than ON user.id = history.user_id in JOINs.

However, the weight of opinion on this issue generally seems to run against me here on Stackoverflow, where most people prefer example 2.

Incidentally, I prefer UserID to user_id as a column naming convention. I don't like typing underscores, and the use of the underscore as the common SQL single-character-match character can sometimes be a little confusing.

Solution 3:

ID is the worst PK name you can have in my opinion. TablenameID works much better for reporting so you don't have to alias a bunch of columns named the same thing when doing complex reporting queries.

It is my personal belief that columns should only be named the same thing if they mean the same thing. The customer ID does not mean the same thing as the orderid and thus they should conceptually have different names. WHen you have many joins and a complex data structure, it is easier to maintain as well when the pk and fk have the same name. It is harder to spot an error in a join when you have ID columns. For instance suppose you joined to four tables all of which have an ID column. In the last join you accidentally used the alias for the first table and not the third one. If you used OrderID, CustomerID etc. instead of ID, you would get a syntax error because the first table doesn't contain that column. If you use ID it would happily join incorrectly.

Solution 4:

I tend to go with the first option, user_id.

If you go with id, you usually end up with a need to alias excessively in your queries. If you go with more_complicated_id, then you either must abbreviate, or you run out of room, and you get tired of typing such long column names.

2 cents.

Solution 5:

I agree with @InSane and like just Id. And here's why:

If you have a table called User, and a column dealing with the user's name, do you call it UserName or just Name? The "User" seems redundant. If you have a table called Customer, and a column called Address, do you call the column CustomerAddress?

Though I have also seen where you would use UserId, and then if you have a table with a foreign key to User, the column would also be UserId. This allows for the consistency in naming, but IMO, doesn't buy you that much.