Naming the MYSQL id column

What is the best practice for naming the column that is the primary key for a mysql table? I have seen two ways:

1. 'id'
2. tableName_id (Where tab开发者_如何学PythonleName is substituted for the name of the table).

I would have thought just id would suffice, because you can still uniquely identify the column by selecting the column proceeded by a period and the table name (i.e. for the column 'id' in the table 'cars', you could select it as cars.id)

Or am I over-thinking it all?

I usually name them as [tblname]_id. The reason is simple. Let's say I have two tables:

member
    member_id
    member_alias
    ...

post
    post_id
    member_id
    post_text
    ...

Now I can join them with MySQLs USING-syntax and save a few characters for each join:

SELECT post.*, member_id, member_alias FROM post
    INNER JOIN member USING (member_id)

Of course, this is all mostly subjective.

MySQL brings no specific considerations beyond being an SQL database. So the question becomes what naming convention should be used for an SQL database.

Noting that you can really name columns whatever you like (within the range of allowed names), when evaluating the best choice of names, consider:

1. how conveniently will the names interact with SQL language features - joins in particular?
2. how well will the names aid comprehension?

Regarding 1, consider:

• How does the USING clause behave?
• How does the NATURAL JOIN clause behave?
• How does INNER JOIN behave when non-join columns from different source tables have the same name and are SELECTed? (Although SQL allows dotted names to defer conflicts - this does not extend to subqueries.)

Regarding 2, consider that as your schema evolves, what is primary may become foreign. What is one may become many. Does it help to rename columns just because cardinality changes?

Finally, consider that a single-column primary key is not always an artificial key. In this example it might actually be a foreign key (1-1 relationship ):

TASK {task_id, task_created_date} -- task with task_id was created on task_created_date

TASK_DUE {task_id, task_due_date} -- task with task_id is due on due_date

I use "ID" for the current table's primary key and tableName_id for a foreign key field, but it usually comes down to personal preference.

I've worked at companies that use tblMyTable for the table name, and then use that as a prefix for every single field in the table. I don't like doing it that way though.

I always go tablename_id. That way I can use USING.

Also, if you're being bad and not specifying table aliases in multi table joins, your sql will be slightly clearer.