Database design - primary key naming conventions

Question

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.

Answer 1

In response to Tomas' answer, there will still be ambiguity assuming that the PK for the comment table is also named id.

In response to the question, Example 1 gets my vote. [table name]_id would actually remove the ambiguity.

Instead of

SELECT u.id AS user_id, c.id AS comment_id FROM user u JOIN comment c ON u.id=c.user_id

I could simply write

SELECT user_id, comment_id FROM user u JOIN comment c ON u.user_id=c.user_id

There's nothing ambiguous about using the same ID name in both WHERE and ON. It actually adds clarity IMHO.

Answer 2

I would suggest example 2. That way there is no ambiguity between foreign keys and primary keys, as there is in example 1. You can do for instance

SELECT * FROM user, comment WHERE user.id = comment.user_id

which is clear and concise.

The third example is redundant in a design where all id's are used as primary keys.

Answer 3

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.