数据库表中ID列的命名 [英] Naming of ID columns in database tables

问题描述

我想知道人们对数据库表中ID列的命名有何看法.

I was wondering peoples opinions on the naming of ID columns in database tables.

如果我有一个名为Invoices的表，且该表的主键为标识列，则将其称为InvoiceID列，这样我就不会与其他表发生冲突，这很明显.

If I have a table called Invoices with a primary key of an identity column I would call that column InvoiceID so that I would not conflict with other tables and it's obvious what it is.

我目前在哪里工作，他们已经将所有ID列称为ID.

Where I am workind current they have called all ID columns ID.

因此他们将执行以下操作:

So they would do the following:

Select  
    i.ID 
,   il.ID 
From
    Invoices i
    Left Join InvoiceLines il
        on i.ID = il.InvoiceID

现在，我在这里看到了一些问题:
1.您需要为select
上的列加上别名 2. ID = InvoiceID不适合我的大脑
3.如果您没有对表进行别名并引用了InvoiceID，那么很明显它在哪个表上?

Now, I see a few problems here:
1. You would need to alias the columns on the select
2. ID = InvoiceID does not fit in my brain
3. If you did not alias the tables and referred to InvoiceID is it obvious what table it is on?

其他人对此主题有何看法?

What are other peoples thoughts on the topic?

推荐答案

ID是SQL反模式. 请参见 http://www.amazon.com/s/ref=nb_sb_ss_i_1_5?url=search-alias%3Dstripbooks&field-keywords=sql+antipatterns&sprefix=sql+a

ID is a SQL Antipattern. See http://www.amazon.com/s/ref=nb_sb_ss_i_1_5?url=search-alias%3Dstripbooks&field-keywords=sql+antipatterns&sprefix=sql+a

如果您有许多ID为ID的表，则报告起来会更加困难.它掩盖了含义，使复杂的查询更难以阅读，并且要求您使用别名来区分报告本身.

If you have many tables with ID as the id you are making reporting that much more difficult. It obscures meaning and makes complex queries harder to read as well as requiring you to use aliases to differentiate on the report itself.

如果某人愚蠢到可以在可用的数据库中使用自然联接，那么您将联接到错误的记录.

Further if someone is foolish enough to use a natural join in a database where they are available, you will join to the wrong records.

如果您想使用某些数据库允许的USING语法，则不能使用ID.

If you would like to use the USING syntax that some dbs allow, you cannot if you use ID.

如果使用ID，如果您碰巧正在复制连接语法(不要告诉我，没有人这样做！)，并且忘记在连接条件中更改别名，则很容易以错误的连接结尾.

If you use ID you can easily end up with a mistaken join if you happen to be copying the join syntax (don't tell me that no one ever does this!)and forget to change the alias in the join condition.

现在您拥有了

select t1.field1, t2.field2, t3.field3
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t1.id = t3.table2id

您的意思

select t1.field1, t2.field2, t3.field3 
from table1 t1 
join table2 t2 on t1.id = t2.table1id
join table3 t3 on t2.id = t3.table2id

如果将tablenameID用作id字段，则这种意外错误的发生率将大大降低，并且更容易发现.

If you use tablenameID as the id field, this kind of accidental mistake is far less likely to happen and much easier to find.

这篇关于数据库表中ID列的命名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！