Geek Answers Handbook

SQL - identifier column names

I always wondered what the pros and cons of these ID name styles in SQL are:

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 do some developers use "id" in each table, some prefix is ​​it with one letter of the table name or with the full name of the table along with one underscore?

+4
source share
7 answers

All this is a personal preference. I personally use Id simply because I consider each table to be my own entity ... when I refer to a key, it becomes CustomerId or OrderId depending on the name of the table.

+7
source

Subjective, but I like to use named identifiers (e.g. customer_id, item_id, etc.)

My reasoning is that if you name your foreign keys in sequence, this will simplify the union, it is always a.customer_id = b.customer_id. Otherwise, with complex queries that use many connections, you have a sea of ​​"id" columns, and it’s not immediately clear what is happening with what.

ETA:

In addition, if you use MySQL, you can use simpler join syntax, for example:

 FROM customers INNER JOIN orders USING customer_id
+11
source

The advantages of using "id" is that you have a uniform field, and it is easy to remember and enter.

The advantages of the id prefix with the table name are that it is easier to work with when often working with multiple tables.

cid seems to be the worst of the three options, without any advantages of the other two.

+2
source

Native SQL: it is best to use something more specific than id so that people writing sql do not always have a prefix with a table alias. By eliminating the need for aliases, you can significantly reduce the size of SQL while eliminating many errors.

The prefix, even with a table name, is no more characters than a more specific column name:

  customer.id
 customer_id

On the other hand, since the foreign key column refers to the table, why not just use the table name as the foreign key?

  table order (
      id SERIAL PRIMARY KEY,
      customer INTEGER NOT NULL REFERENCES (customer)
 ...

Then we have:

  FROM customer INNER JOIN order ON customer.id = order.customer
+2
source

Some ORMs work "better" when you name the primary key of each table as "id".

+1
source

The first solution is "id" or not, and it is controlled by a tool that processes SQL:

  • ORM: some prefer 'id'
  • Native SQL: it is best to use something more specific than id, so people who write sql do not always need a prefix with a table alias. By eliminating the need for aliases, you can significantly reduce the size of SQL while eliminating many errors.

The second solution, if you are using an ORM requiring "id":

  • You can simply use ORM constraints for all column names
  • Or you can name the columns for people and create a separate view that renames the columns depending on what the ORM wants. This is a bit of work, but if you have more than just an ORM looking at tables (reporting tools, other ORMs, etc.), this can be useful.

Or the second solution, if you are not using 'id', is how to create names in relational databases:

  • Usually you do not have a case to use - therefore camelcase, etc. does not work.
  • You want to avoid name collisions.
  • You want the names to be a little intuitive without knowing the table name
  • You want the names to be formatted sequentially.
  • So cid is not very good. cache_id is preferred.
+1
source

As noted, several others: this is truly a personal preference.

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

The advantage of the first statement is that you can rename your table without renaming the id column to reflect this change. But this is hardly a reason to make it a doctrine.

0
source

More articles:


All Articles