(Database Design) why in the database tables most of the times primery key name is: <tablename_id> and not <id>?

Question

Hi, why in the database tables most of the times primery key name is: tablename_id and not id? example: user_id and not just id. Thanks

Answer 1

What makes you think they are always named something_id? Sometimes I use something_KEY like if I am building dimension tables. I don't think I have ever made a key/id with tablename_id. In short, I think it depends on the situation, the architect, and your standards (i.e., all caps, underscores, etc).

Answer 2

This is totally a local convention, and will vary dramatically depending on your company's practice (or the project you're working with).

As for me, I pretty much always choose "ID" because I'm very comfortable with the table aliasing idiom in SQL queries. For me select p.id from products p is more comfortable than select product_id from products. But I am not everybody else. I value consistency and I prefer to have as close as possible a mapping between my object model and my database; from my perspective, using "id" is more predictable across the domain. Some people prefer to have unique-as-possible names so that they don't accidentally refer to the wrong "id" by accident.

Answer 3

I've seen a few cases of database schemas where all columns have been prefixed with either the table name or an abbreviation of the table name. The explanation I got when questioning this was that it was practical not having to worry about column name collisions when joining tables, because each column then got a name that was unique in the schema.

I guess that this applies to primary keys as well, especially as they are frequently used when doing joins.

In my opinion, this doesn't make sense, because it is very easy to just prefix the column name with the table name using dot notation in the sql queries, and thereby removing the ambiguity... if at all you have to write the SQL yourself, that is.

Answer 4

I use a similar convention: TableNameID because:

• foreign keys can have the same column name, which helps when you have lots of tables
• selecting columns form multiple tables with the same name forces you to use an alias, so naming the column more uniquely helps.
• My tables are not mapped to objects in the application, so don't need to consider that.

Answer 5

This syntax can make it easier when doing joins, avoiding the 'as' keyword. ex:

SELECT `tbl_user`.`user_id`, `tbl_emails`.`email_id` FROM `tbl_emails` LEFT JOIN `tbl_users` ON 'tbl_emails`.`user_id` = `tbl_users`.`user_id`

rather than:

SELECT SELECT `tbl_user`.`id` AS `user_id`, `tbl_emails`.`id` AS `email_id` FROM `tbl_emails` LEFT JOIN `tbl_users` ON 'tbl_emails`.`user_id` = `tbl_users`.`id`

However, this is only a preference that some coders feel. Coding guidelines and naming standards are different everywhere. I personally use only a simple 'id' for the primary key, then use the 'as' keywords. There's not necessarily a right or wrong, as long as what you do makes sense and you do it consistently.

Answer 6

When you have some JOIN then ON A.customer_id = B.customer_id looks better than ON A.customer_id = B.id. If you don't use JOINs, I see no real reason for tablename_id. About JOIN on Wikipedia

Answer 7

Naming the primary key the same as its foreign keys is necessary if you want to use the NATURAL JOIN syntax. I think it's easier to understand as well, but there are people who feel strongly about it either way. In any case, it's really only a matter of style and local convention -- whatever you decide to do, at least be consistent!

Answer 8

A sound principle of database naming schemes is that a name should uniquely identify the thing being named within the domain of discourse (see ISO 11179 for example). It is also a convention in relational database design that a foreign key should have the same name as the candidate key that it references.

"id" is certainly a poor name for anything because it tells us nothing about the attribute being named and it is presumably unlikely to be unique within the database.

Answer 9

Thank you very much for good anwers!

Answer 10

I wish those who label PK columns as ID would offer a list of benefits.

You get responses like this:

it is very easy to just prefix the column name with the table name using dot notation

User_ID or User.ID

That's not an advantage of just ID as a name. What does it do for you that USER_ID won't do?

Or

I pretty much always choose "ID" because I'm very comfortable with the table aliasing

That's awesome this guy is comfortable, what's the benefit?

Here's my argument:

Would you have any other n columns all named exactly the same, all meaning completely different things? Would you allow the column "COST" to mean 5 different things in five different tables? Wouldn't you want to call them, "TOTAL_COST", "DIRECT_COST", "AVERAGE_COST", etc? SO why would you do that with ID? If you wouldn't do that for any other column, be consistent.

I use the dictionary tables a lot. I like to query the database's own tables for discovery. Sometimes you'll find missing referential integrity constraints. If the columns are named the same, you can find most missing constraints with no other information. You look for PK's, get the column name USER_ID, Look for other tables with USER_ID, check to see if it has a constraint. If you name your PK ID, you'll also have to know what the convention is Is the FK column named, %Table%_ID or %Table%ID or %Table%_FK or %Table%FK? A tautological column naming convention is much simpler than some function.