Id or [TableName]Id as primary key / entity identifier

Question

Is it preferred to use "Id" as the column name for a primary key or "[TableName]Id" as a naming convention?

Table: Account
Primary Key: Id

-- versus --

Table: Account
Primary Key: AccountId

It seems to be split about 50% / 50% in the implementations that I've seen. What are the advantages and disadvantages in each approach?

Follow-up:

Does it make sense to use one convention in my database, and another on my entities in code? Or should I keep them consistent? How would does this work best in most ORMs?

Answer 1

The first method is more likely OOP naming convention.

The second method has the advantage of avoiding ambiguous column names in join queries. Although you can use an alias, sometimes this is not possible, like in some ORM frameworks (EntitySpaces comes to mind).

Answer 2

Duplicate: Naming of ID columns in database tables

Answer 3

TableNameID for clarity

1. Improve JOIN readability
2. Clarity, say, when multiple FK "ID" columns (PK matches FK)
3. ID is a reserved keyword

Answer 4

I use ID. How would you set up User table with an Account ID as a foreign key? Would you name that column AccountAccountID or AccountID?

But the most important part is to be consistent in my opinion, whichever way you choose.

Edit: I think in reading my comment the logic of my argument is off, since obviously you wouldn't ever call a field AccountAccountID. But at first glance using [TableName]ID as the primary key and [TableName]ID as the foreign key as well feels weird to me. It seems like you're using two different rules for two things that should follow the same set of standards. Also I think that Account.ID and User.AccountID is more readable and semantically correct.

Answer 5

I agree with you. It's a split. Id by itself is not very descriptive though. Normally i don't use Id because it's a hell of alot safer to use AccountId when dealing with the possible risk of sql injection.

Answer 6

I found that the explicit naming (TableId) is better. For start all foreign keys have a natural name this way ([RelatedTable]Id). And also I always end up returning joined queries with two types of Id's anyway, where I'm be forced to alias them properly so the client can distinguish between AccountId and ClientId. Using explicit key names also simplifies my data access/orm layer logic as it doesn't have to deal with ambiguity, eg. account type key is always 'AccountId', not 'Id' in some queryies and 'AccountId' in other. My 2c.

Answer 7

Well I use one scheme all the time and find it very useful.

The primary key in a table is always called "ID", with splittet keys I call the column with the row-identing information the "ID" otherwise no "ID" column is called.

All foreign Keys use the name of the table they are referencing.

CREATE TABLE `Person`  
(
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `FirstName` VARCHAR(255)  NOT NULL,
  `LastName` VARCHAR(255)  NOT NULL,
  PRIMARY KEY (`ID`)
);

CREATE TABLE `Tutorial`
(
  `ID` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `Name` VARCHAR(255) NOT NULL,
  PRIMARY KEY (`ID`)
);

CREATE TABLE `Class`
(
  `Person` INTEGER UNSIGNED NOT NULL,
  `Tutorial` INTEGER UNSIGNED NOT NULL
  PRIMARY KEY (`Person`, `Tutorial`)
);

Answer 8

Avoid common words like ID, status, description as column names.

use names like WarehouseID, WarehouseStatus, WarehouseDescription, these will make your life easier when you write your queries, search code, read old code, etc.