SQL - naming of ID columns

Question

I've always wondered what are the pros and the cons of these ID naming styles in SQL:

CREATE TABLE cache (
id INT AUTO_INCREMENT,
PRIMARY KEY(id)
);

CREATE TABLE cache (
cid INT AUTO_INCREMENT,
PRIMARY KEY(id)
);

CREATE TABLE cache (
cache_id INT AUTO_INCREMENT,
PRIMARY KEY(id)
);

Why some developers use "id" in each table, some prefix it with one letter of the table name or with the entire table name along with one underscore?

Answer 1

It's all a personal preference. I personally use Id simply because I think of each table as its own entity...then when I reference with a key it becomes CustomerId or OrderId depending on the name of the table.

Answer 2

Subjective, but I like to use named ids (e.g. customer_id, item_id, etc.)

My reasoning is that if you name your foreign keys consistently it makes joins easier to understand - it's always a.customer_id = b.customer_id. Otherwise, with complicated queries that use lots of joins, you have a sea of "id" columns and it's not immediately obvious what goes with what.

ETA:

Also, if you are using MySQL, you can use the simpler join syntax, e.g.:

FROM customers INNER JOIN orders USING customer_id