Geek Answers Handbook

Id or [TableName] Id as the primary key / object identifier

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

Table: Account
Primary Key: Id

- vs -

Table: Account
Primary Key: AccountId

It seems that in the implementations I saw it seems like 50% / 50%. What are the advantages and disadvantages of each approach?

Subsequent:

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

+4
source share
7 answers

TableNameID for clarity

  • Improving the readability of JOIN
  • Clarity, for example, when multiple columns are FK "ID" (PK corresponds to FK)
  • ID - Reserved Keyword
+14
source

I am using ID. How to configure user table with account id as foreign key? Would you call this column AccountAccountID or AccountID?

But the most important part should be consistent, in my opinion, depending on which one you choose.

Change I think that when reading my comment, the logic of my argument is disabled, because obviously you would never have called the AccountAccountID field. But at first glance, using the [TableName] identifier as the primary key and the [TableName] identifier as the foreign key also seems strange to me. It looks like you are using two different rules for two things that must follow the same set of standards. I also think that Account.ID and User.AccountID are more readable and semantically correct.

+7
source

Avoid common words like identifier, status, description like column names.

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

+3
source

The first method is a more likely OOP naming convention.

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

+2
source

I found that explicit naming (TableId) is better. To begin with, all foreign keys have a natural name this way ([RelatedTable] Id). In addition, I always return related requests with two types of Identifiers in any case when I am forced to pseudonize them correctly so that the client can distinguish between AccountId and ClientId. Using explicit key names also simplifies my / orm data access logic, as it does not require ambiguity, for example. the account type key is always "AccountId", not "Id" in some requests and "AccountId" in another. My 2c.

+2
source

I agree with you. This is a split. Identification in itself is not very descriptive. As a rule, I do not use an identifier, because it is very safe to use AccountId when you are dealing with a possible risk of sql implementation.

0
source

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

The primary key in the table is always called "ID", with splittet keys. I call the column with information about the row identifier "ID", otherwise the column "ID" is not called.

All foreign keys use the name of the table to which they refer.

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`) );
0
source

More articles:


All Articles