命名表的列与表名前缀好或不好？

Below are two same tables with different column naming conventions which one is better and why ??

下面是两个相同的表,具有不同的列命名约定,哪一个更好,为什么?

<<User>>
 id
 name
 age

<<User>>
user_id
user_name
user_age

A clear advantage I see is when it comes to joining tables same column name of two different tables can get overlap if not aliased properly with 'AS' . But with the latter there is no such need of it but still I find lots of projects using the first structure? Why is that?

我看到一个明显的优势是,当涉及到连接表时,如果没有使用'AS'正确别名,则两个不同表的相同列名称可以重叠。但是后者没有这样的需要,但我仍然发现许多项目使用第一个结构?这是为什么?

7 个解决方案

#1

I think I personally prefer

我想我个人更喜欢

<<User>>
  id
  name
  age

I think because SELECT user.id, user.name FROM user is perfectly readable and understandable. I don't see much benefit from the overly verbose user.user_id. Also, when writing object orientated code we wouldn't call a user object's id user_id so why do it in a database?

我认为因为SELECT user.id,user.name FROM user是完全可读和易懂的。我没有从过于冗长的user.user_id中看到太多的好处。另外,在编写面向对象的代码时,我们不会调用用户对象的id user_id,那么为什么要在数据库中呢?

A clear advantage I see is when it comes to joining tables same column name 
of two different tables can get overlap if not aliased properly with 'AS'

I see your point but is writing this

我明白你的意思但是正在写这个

SELECT user_id FROM user 
INNER JOIN account ON account.account_id = user.account_id

any better than

比任何更好

SELECT user.id FROM user
INNER JOIN account ON account.id = user.account_id

In the first example if the account table also has a user_id field we would still have to prefix the SELECT user_id FROM... with SELECT user.user_id FROM...

在第一个示例中,如果帐户表也有user_id字段,我们仍然必须在SELECT user_id FROM ...前面加上SELECT user.user_id FROM ...

As far as I can see the second example is much better.

据我所知,第二个例子要好得多。

#2

I personally do not use prefixes. Not for columns and not for tables (tblSomething is just a ridiculous name).

我个人不使用前缀。不是列而不是表(tblSomething只是一个荒谬的名字)。

One exception though (there always is one, isn't there?) is for ID columns.

但有一个例外(总有一个,不存在吗?)是ID列。

Whereas all other column names usually relate strongly to the table itself and can easily be spotted in a SQL statement without a prefix, ID columns are different.

虽然所有其他列名通常与表本身强烈相关,并且可以在没有前缀的SQL语句中轻松发现,但ID列是不同的。

So I find myself often using something like: user_id, firstname, lastname

所以我发现自己经常使用类似:user_id,firstname,lastname

#3

That style came from Oracle hackers in the olden days when dinasours ruled the earth. DO NOT USE IT. It is painful and ugly. I strongly discourage this practice.

这种风格来自甲骨文黑客,在过去的恐龙统治地球的过去。不要使用它。这是痛苦和丑陋的。我强烈反对这种做法。

#4

In a recent database design class my lecturer was encouraging the prefixed convention. It just looked gross.

在最近的数据库设计课中,我的讲师鼓励使用前缀约定。它看起来很糟糕。

#5

We use a similar convention as your first example at work, on our Oracle boxes, and to be frank I hate it.

我们使用类似的约定作为你工作的第一个例子,在我们的Oracle盒子上,坦率地说我讨厌它。

For me, I think it's massively redundant; but I do accept that in large queries it can help debugging, it's very visually obvious.

对我来说,我认为这是多余的;但我确实接受在大型查询中它可以帮助调试,它在视觉上非常明显。

Which one is better ? There's no good answer; which one makes you feel less bad! That's the only true answer.

哪一个更好 ?没有好的答案;哪一个让你感觉不那么糟糕!这是唯一真实的答案。

#6

Complex systems often use a metadata repository. Metadata repository require data elements to be unique. So having, say, 147 tables with the same data element name (id), but different data definitions (user id, account id, etc.) wouldn't work.

复杂系统通常使用元数据存储库。元数据存储库要求数据元素是唯一的。因此,具有相同数据元素名称(id)的147个表,但不同的数据定义(用户ID,帐户ID等)将不起作用。

From that point of view the "user" part of "user_id" isn't the name of the table that "user_id" happens to be in. Instead, "user" is the object class term, and "id" one of the properties of that object class. I'm using object, class, and property not in the OO programming sense, but in the ISO 11179 Metadata Standard sense.

从这个角度来看,“user_id”的“user”部分不是“user_id”碰巧所在的表的名称。相反,“user”是对象类术语,而“id”是其中一个属性那个对象类。我使用的对象,类和属性不是在OO编程意义上,而是在ISO 11179元数据标准意义上。

You can find drafts of ISO 11179 online. I think it's worth reading.

您可以在线查找ISO 11179的草稿。我觉得值得一读。

If you're using multiple columns, all named "id", in a single query, you have to alias at least some of them anyway. What are the odds that 40 different programmers, working over a period of 10 or 15 years, are going to use the same alias every time? In big systems, there's a lot of value in knowing that the user's id number will be named "user_id" everywhere it appears. (Except possibly in a self-join.) I've worked on systems where the user's id number was "user_id", "usr_id", "u_id", "u_no", "u_uno", "u_n", "u", "uid", and so on. Takes time. Time that can be better used.

如果您在单个查询中使用多个列(全部名为“id”),则无论如何都必须至少为其中一些列添加别名。 40名不同程序员在10年或15年的时间内每次使用相同的别名的几率是多少?在大型系统中,知道用户的ID号在其出现的任何地方都将被命名为“user_id”,这有很多价值。 (除了可能在自联接中。)我已经在用户的id号为“user_id”,“usr_id”,“u_id”,“u_no”,“u_uno”,“u_n”,“u”的系统上工作过, “uid”,依此类推。需要时间。可以更好地利用的时间。

#7

It is not only answer , but this is how i set up DB structure:

它不仅是答案,而且这是我设置DB结构的方式:

table names are capitalized and plural : Users, Documents, Products

表名大写和复数:用户,文档,产品

column names are lowercase

列名称是小写的

primary keys contains table prefix in singular: user_id, document_id

主键包含单数的表前缀:user_id,document_id

foreign keys have the same name as primary keys they are referring to

外键与它们所指的主键具有相同的名称

So for example in case of users and documents :

例如,在用户和文档的情况下:

Users                  Documents
--------               ----------
user_id PK ----.       document_id PK
nickname        `----- user_id FK
hash                   title
                       content

In such table to select all document titles for user with nickname = "wintermute" you would write:

在这样的表中为昵称=“wintermute”的用户选择所有文档标题,你会写:

SELECT 
  Documents.title AS title 
FROM Documents
  LEFT JOIN Users USING(user_id)
WHERE nickname = 'wintermute'

#1