SQL - naming of ID columns.

Question

Hi,

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

Some ORMs work "better" when you name each table's primary key as "id".

Answer 3

The pros of just using 'id' is that you have a uniform field, and it's easy to remember and type.

The pros of prefixing id with the table name is that it can be easier to work with when working with multiple tables frequently.

cid seems like the worst of three options, with none of the benefits of the other two.

Answer 4

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

Answer 5

The first decision is 'id' or not, and it is driven by tool that's handling the SQL:

• ORM: some prefer 'id'
• Native SQL: its best to use something more specific than id so that the humans writing the sql don't always have to prefix with a table alias. By eliminating the need for aliases you can significantly shrink the size of the SQL and eliminate a lot of errors at the same time.

The second decision, if you're using an ORM that demands 'id', is:

• You can just go with ORM constraints on all column names
• Or you can name the columns for humans, and create a separate view that renames the columns to what the ORM wants. This is a bit of work, but if you have more than just the ORM looking at the tables (reporting tools, other ORMs, etc) it may be worthwhile.

Or the second decision, if you're not using 'id', is - how to build names in a relational databases:

• You typically don't have case to use - so camelcase, etc don't work
• You want to avoid naming collisions
• You want names to be a little intuitive without knowing the table name
• You want names to be consistently formatted
• So, cid isn't very good. cache_id is preferable.

Answer 6

As a few other have already pointed out: It's really a personal preference.

I more or less stick to the third approach (table foo will get the id column "foo_id") but cannot really tell you why ;-)

An advantage of the first approch ist that you can rename your table without having to rename your id column to reflect the change. But that's hardly a reason to make it a doctrine.