why within the database tables the majority of the occasions primery key title is: tablename_id and never id? example: user_id and not simply id. Thanks

I personally use an identical convention: TableNameID because:

  • foreign secrets can have a similar column title, which will help if you have plenty of tables
  • choosing posts form multiple tables with similar title can make you make use of an alias, so naming the column more distinctively helps.
  • My tables aren't planned to things within the application, so don't have to take into account that.

A seem principle of database naming schemes is the fact that a title should distinctively identify the one thing being named inside the domain of discourse (see ISO 11179 for instance). It's also a convention in relational database design that the foreign key should have a similar title because the candidate key it references.

"id" is unquestionably an undesirable reputation for anything since it informs us nothing concerning the attribute being named which is most probably unlikely to become unique inside the database.

Why is you believe they're always named something_id? Sometimes I personally use something_KEY like should i be building dimension tables. I do not think I've ever designed a key/id with tablename_id. In a nutshell, It is dependent around the situation, the architect, as well as your standards (i.e., all caps, underscores, etc).

This really is totally a nearby convention, and can vary significantly based on your company's practice (or even the project you are dealing with).

For me, I virtually always choose "ID" because I am very confident with the table aliasing idiom in SQL queries. For me personally select p.id from products p is much more comfortable than select product_id from products. But I'm not everyone else. I value consistency and that i would rather have as near as you possibly can a mapping between my object model and my database from the perspective, using "id" is much more foreseeable over the domain. Many people would rather have unique-as-possible names to ensure that they do not accidentally make reference to the incorrect "id" accidentally.

If only individuals who label PK posts as ID would offer a listing of advantages.

You receive reactions such as this:

it's very simple to just prefix the column title using the table title using us dot notation

User_ID or User.ID

That isn't a benefit of just ID like a title. Exactly what does it do for you personally that USER_ID will not do?


I virtually always choose "ID" because I am very confident with the table aliasing

That's awesome this person feels safe, what is the benefit?

Here's my argument:

Can you have other n posts all named the identical, all meaning different things? Can you permit the column "COST" to mean 5 various things in five different tables? Wouldn't you need to give them a call, "TOTAL_COST", "DIRECT_COST", "AVERAGE_COST", etc? Kind you accomplish that with ID? Should you wouldn't do this for just about any other column, remain consistent.

I personally use the dictionary tables a great deal. I love to query the database's own tables for discovery. Sometimes you will find missing referential integrity constraints. When the posts are named exactly the same, you'll find most missing constraints without any additional information. You search for PK's, obtain the column title USER_ID, Search for other tables with USER_ID, determine if it features a constraint. Should you title your PK ID, you'll also need to understand what the convention it's the FK column named, %Table%_ID or %Table%ID or %Table%_FK or %Table%FK? A tautological column naming convention is a lot simpler than some function.