database 数据库、表和列命名约定？

Database, Table and Column Naming Conventions?

提问by GateKiller

Whenever I design a database, I always wonder if there is a best way of naming an item in my database. Quite often I ask myself the following questions:

每当我设计一个数据库时，我总是想知道是否有一种最好的方法来命名我的数据库中的项目。我经常问自己以下问题：

1. Should table names be plural?
2. Should column names be singular?
3. Should I prefix tables or columns?
4. Should I use any case in naming items?

1. 表名应该是复数吗？
2. 列名应该是单数吗？
3. 我应该为表或列添加前缀吗？
4. 我应该在命名项目时使用任何大小写吗？

Are there any recommended guidelines out there for naming items in a database?

是否有任何推荐的指南来命名数据库中的项目？

采纳答案by urini

I recommend checking out Microsoft's SQL Server sample databases: https://github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

我建议查看 Microsoft 的 SQL Server 示例数据库：https: //github.com/Microsoft/sql-server-samples/releases/tag/adventureworks

The AdventureWorks sample uses a very clear and consistent naming convention that uses schema names for the organization of database objects.

AdventureWorks 示例使用非常清晰且一致的命名约定，该约定使用架构名称来组织数据库对象。

1. Singular names for tables
2. Singular names for columns
3. Schema name for tables prefix (E.g.: SchemeName.TableName)
4. Pascal casing (a.k.a. upper camel case)

1. 表的单数名称
2. 列的单数名称
3. 表前缀的模式名称（例如：SchemeName.TableName）
4. Pascal 外壳（又名上驼色外壳）

回答by Patrick Karcher

Late answer here, but in short:

在这里回答晚了，但简而言之：

1. My preferenceis plural
2. Yes
3. Tables: *Usually* no prefixes is best. Columns: No.
4. Both tables and columns: PascalCase.

1. 我的偏好是复数
2. 是的
3. 表格：*通常*最好没有前缀。 列：没有。
4. 表和列：PascalCase。

Elaboration:

细化：

(1) What you must do.There are very few things that you mustdo a certain way, every time, but there are a few.

(1)你必须做什么。每次都必须以某种方式做的 事情很少，但也有一些。

• Name your primary keysusing "[singularOfTableName]ID" format. That is, whether your table name is Customeror Customers, the primary key should be CustomerID.
• Further, foreign keys mustbe named consistentlyin different tables. It should be legal to beat up someone who does not do this. I would submit that while defined foreign key constraints are oftenimportant, consistent foreign key naming is alwaysimportant
• You database must have internal conventions. Even though in later sections you'll see me being very flexible, withina database naming must be very consistent . Whether your table for customers is called Customersor Customeris less important than that you do it the same way throughout the same database. And you can flip a coin to determine how to use underscores, but then you must keep using them the same way. If you don't do this, you are a bad person who should have low self-esteem.

• 使用“[singularOfTableName]ID”格式命名主键。也就是说，无论您的表名是Customer还是Customers，主键都应该是CustomerID。
• 此外，必须在不同的表中一致地命名外键。殴打不这样做的人应该是合法的。我认为虽然定义的外键约束通常很重要，但一致的外键命名始终很重要
• 您的数据库必须具有内部约定。尽管在后面的部分中您会看到我非常灵活，但在数据库中命名必须非常一致。您的客户表是称为客户还是客户并不重要，因为您在同一数据库中以相同的方式执行此操作。你可以抛硬币来决定如何使用下划线，但你必须继续以同样的方式使用它们。如果你不这样做，你就是一个应该自卑的坏人。

(2) What you should probably do.

(2)你大概应该做什么。

• Fields representing the same kind of data on different tables shouldbe named the same. Don't have Zip on one table and ZipCode on another.
• To separate words in your table or column names, use PascalCasing. Using camelCasing would not be intrinsically problematic, but that's not the convention and it would look funny. I'll address underscores in a moment. (You may not use ALLCAPS as in the olden days. OBNOXIOUSTABLE.ANNOYING_COLUMN was okay in DB2 20 years ago, but not now.)
• Don't artifically shorten or abbreviate words. It is better for a name to be long and clear than short and confusing. Ultra-short names is a holdover from darker, more savage times. Cus_AddRef. What on earth is that? Custodial Addressee Reference? Customer Additional Refund? Custom Address Referral?

• 表示不同表上相同类型数据的字段应命名相同。不要在一张桌子上放邮编，而在另一张桌子上放邮编。
• 要分隔表名或列名中的单词，请使用 PascalCasing。使用camelCasing 本质上不会有问题，但这不是惯例，它看起来很有趣。稍后我将介绍下划线。（您可能不像过去那样使用 ALLCAPS。OBNOXIOUSTABLE.ANNOYING_COLUMN 20 年前在 DB2 中还可以，但现在不行了。）
• 不要人为地缩短或缩写单词。名字长而清晰总比短而混乱好。超短名称是更黑暗、更野蛮时代的延续。Cus_AddRef。那到底是什么？托管收件人参考？客户额外退款？自定义地址推荐？

(3) What you should consider.

(3)你应该考虑什么。

• I really think you should have plural names for tables; some think singular. Read the arguments elsewhere. Column names should be singular however. Even if you use plural table names, tables that represent combinations of other tables might be in the singular. For example, if you have a Promotionsand an Itemstable, a table representing an item being a part of a promotion could be Promotions_Items, but it could also legitimately be Promotion_Items I think (reflecting the one-to-many relationship).
• Use underscores consistently and for a particular purpose. Just general tables names should be clear enough with PascalCasing; you don't need underscores to separate words. Save underscores either (a) to indicate an associative table or (b) for prefixing, which I'll address in the next bullet.
• Prefixing is neither good or bad. It usuallyis not best. In your first db or two, I would not suggest using prefixes for general thematic grouping of tables. Tables end up not fitting your categories easily, and it can actually make it harderto find tables. With experience, you can plan and apply a prefixing scheme that does more good than harm. I worked in a db once where data tables began with tbl, config tables with ctbl, views with vew, proc's sp, and udf's fn, and a few others; it was meticulously, consistently applied so it worked out okay. The only time you NEED prefixes is when you have really separate solutions that for some reason reside in the same db; prefixing them can be very helpful in grouping the tables. Prefixing is also okay for special situations, like for temporary tables that you want to stand out.
• Very seldom (if ever) would you want to prefix columns.

• 我真的认为你应该为表格使用复数名称；有些人认为是单一的。阅读别处的论点。但是，列名应该是单数。即使您使用复数表名，表示其他表组合的表也可能是单数。例如，如果您有一个Promotions和一个Items表，则表示作为促销一部分的项目的表可能是 Promotions_Items，但我认为它也可以合法地是 Promotion_Items（反映一对多关系）。
• 始终如一地使用下划线并用于特定目的。使用 PascalCasing 时，一般表名应该足够清楚；你不需要下划线来分隔单词。保存下划线 (a) 表示关联表或 (b) 用于前缀，我将在下一个项目符号中解决。
• 前缀既不好也不坏。这通常不是最好的。在您的第一个或两个数据库中，我不建议对表的一般主题分组使用前缀。表格最终无法轻松满足您的类别，而且实际上会使查找表格变得更加困难。凭借经验，您可以计划和应用一个利大于弊的前缀方案。我曾经在一个 db 工作过，其中数据表以tbl开头，配置表以ctbl 开头，视图以vew、proc 的sp和udf的fn开头，以及其他一些；它一丝不苟，始终如一地应用，所以效果很好。唯一需要前缀的时候是当你有真正独立的解决方案时，由于某种原因驻留在同一个数据库中；为它们添加前缀对于对表进行分组非常有帮助。前缀也适用于特殊情况，例如您想要突出的临时表。
• 您很少（如果有的话）想要为列添加前缀。

回答by Matt Hamilton

Ok, since we're weighing in with opinion:

好的，因为我们正在权衡意见：

I believe that table names should be plural. Tables are a collection (a table) of entities. Each row represents a single entity, and the table represents the collection. So I would call a table of Person entities People (or Persons, whatever takes your fancy).

我认为表名应该是复数。表是实体的集合（表）。每行代表一个实体，表格代表集合。所以我会称一个 Person 实体表为 People（或 Person，任何你喜欢的）。

For those who like to see singular "entity names" in queries, that's what I would use table aliases for:

对于那些喜欢在查询中看到单数“实体名称”的人，这就是我将使用表别名的目的：

SELECT person.Name
FROM People person

A bit like LINQ's "from person in people select person.Name".

有点像 LINQ 的“from person in people select person.Name”。

As for 2, 3 and 4, I agree with @Lars.

至于 2、3 和 4，我同意@Lars。

回答by Guy

I work in a database support team with three DBAs and our considered options are:

我在一个拥有三个 DBA 的数据库支持团队工作，我们考虑的选项是：

1. Any naming standard is better than no standard.
2. There is no "one true" standard, we all have our preferences
3. If there is standard already in place, use it. Don't create another standard or muddy the existing standards.

1. 任何命名标准都比没有标准好。
2. 没有“一个真”的标准，我们都有自己的喜好
3. 如果标准已经到位，请使用它。不要创建另一个标准或混淆现有标准。

We use singular names for tables. Tables tend to be prefixed with the name of the system (or its acronym). This is useful if the system complex as you can change the prefix to group the tables together logically (ie. reg_customer, reg_booking and regadmin_limits).

我们对表使用单数名称。表往往以系统名称（或其首字母缩写词）为前缀。这在系统复杂时很有用，因为您可以更改前缀以在逻辑上将表组合在一起（即 reg_customer、reg_booking 和 regadmin_limits）。

For fields we'd expect field names to be include the prefix/acryonm of the table (i.e. cust_address1) and we also prefer the use of a standard set of suffixes ( _id for the PK, _cd for "code", _nm for "name", _nb for "number", _dt for "Date").

对于字段，我们希望字段名称包含表的前缀/首字母缩略词（即 cust_address1），我们也更喜欢使用一组标准的后缀（_id 表示 PK，_cd 表示“代码”，_nm 表示“名称” ", _nb 代表“数字”，_dt 代表“日期”）。

The name of the Foriegn key field should be the same as the Primary key field.

Foriegn 键字段的名称应与主键字段的名称相同。

i.e.

IE

SELECT cust_nm, cust_add1, booking_dt
FROM reg_customer
INNER JOIN reg_booking
ON reg_customer.cust_id = reg_booking.cust_id

When developing a new project, I'd recommend you write out all the preferred entity names, prefixes and acronyms and give this document to your developers. Then, when they decide to create a new table, they can refer to the document rather than "guess" what the table and fields should be called.

在开发新项目时，我建议您写出所有首选的实体名称、前缀和首字母缩略词，并将此文档提供给您的开发人员。然后，当他们决定创建一个新表时，他们可以参考文档而不是“猜测”应该调用什么表和字段。

回答by Lars M?hlum

1. No. A table should be named after the entity it represents. Person, not persons is how you would refer to whoever one of the records represents.
2. Again, same thing. The column FirstName really should not be called FirstNames. It all depends on what you want to represent with the column.
3. NO.
4. Yes. Case it for clarity. If you need to have columns like "FirstName", casing will make it easier to read.

1. 不可以。表格应该以其代表的实体命名。人，而不是人是您指代其中一条记录所代表的人的方式。
2. 再次，同样的事情。FirstName 列确实不应称为 FirstNames。这一切都取决于您想用列表示什么。
3. 不。
4. 是的。为清楚起见。如果您需要像“FirstName”这样的列，大小写将使阅读更容易。

Ok. Thats my $0.02

好的。那是我的 0.02 美元

回答by onedaywhen

I'm also in favour of a ISO/IEC 11179 style naming convention, noting they are guidelines rather than being prescriptive.

我也赞成 ISO/IEC 11179 风格的命名约定，指出它们是指导方针而不是规定性的。

See Data element name on Wikipedia:

请参阅维基百科上的数据元素名称：

"Tables are Collections of Entities, and follow Collection naming guidelines. Ideally, a collective name is used: eg., Personnel. Plural is also correct: Employees. Incorrect names include: Employee, tblEmployee, and EmployeeTable."

“表是实体的集合，并遵循集合命名准则。理想情况下，使用集合名称：例如，人员。复数也是正确的：员工。不正确的名称包括：员工、tblEmployee 和 EmployeeTable。”

As always, there are exceptions to rules e.g. a table which always has exactly one row may be better with a singular name e.g. a config table. And consistency is of utmost importance: check whether you shop has a convention and, if so, follow it; if you don't like it then do a business case to have it changed rather than being the lone ranger.

与往常一样，规则也有例外，例如总是只有一行的表可能会更好地使用单数名称，例如配置表。一致性至关重要：检查您的商店是否有约定，如果有，请遵守；如果你不喜欢它，那么做一个商业案例来改变它，而不是成为孤独的护林员。

回答by dallin

I hear the argument all the time that whether or not a table is pluralized is all a matter of personal taste and there is no best practice. I don't believe that is true, especially as a programmer as opposed to a DBA. As far as I am aware, there are no legitimate reasons to pluralize a table name other than "It just makes sense to me because it's a collection of objects," while there are legitimate gains in code by having singular table names. For example:

我一直听到这样的争论，即桌子是否复数完全是个人品味的问题，没有最佳实践。我不相信这是真的，尤其是作为程序员而不是 DBA。据我所知，除了“它对我来说很有意义，因为它是一个对象的集合”之外，没有合法的理由将表名复数化，而通过使用单数表名在代码中获得合法收益。例如：

1. It avoids bugs and mistakes caused by plural ambiguities. Programmers aren't exactly known for their spelling expertise, and pluralizing some words are confusing. For example, does the plural word end in 'es' or just 's'? Is it persons or people? When you work on a project with large teams, this can become an issue. For example, an instance where a team member uses the incorrect method to pluralize a table he creates. By the time I interact with this table, it is used all over in code I don't have access to or would take too long to fix. The result is I have to remember to spell the table wrong every time I use it. Something very similar to this happened to me. The easier you can make it for every member of the team to consistently and easily use the exact, correct table names without errors or having to look up table names all the time, the better. The singular version is much easier to handle in a team environment.

2. If you use the singular version of a table name AND prefix the primary key with the table name, you now have the advantage of easily determining a table name from a primary key or vice versa via code alone. You can be given a variable with a table name in it, concatenate "Id" to the end, and you now have the primary key of the table via code, without having to do an additional query. Or you can cut off "Id" from the end of a primary key to determine a table name via code. If you use "id" without a table name for the primary key, then you cannot via code determine the table name from the primary key. In addition, most people who pluralize table names and prefix PK columns with the table name use the singular version of the table name in the PK (for example statuses and status_id), making it impossible to do this at all.

3. If you make table names singular, you can have them match the class names they represent. Once again, this can simplify code and allow you to do really neat things, like instantiating a class by having nothing but the table name. It also just makes your code more consistent, which leads to...

4. If you make the table name singular, it makes your naming scheme consistent, organized, and easy to maintain in every location. You know that in every instance in your code, whether it's in a column name, as a class name, or as the table name, it's the same exact name. This allows you to do global searches to see everywhere that data is used. When you pluralize a table name, there will be cases where you will use the singular version of that table name (the class it turns into, in the primary key). It just makes sense to not have some instances where your data is referred to as plural and some instances singular.

1. 它避免了由复数歧义引起的错误和错误。程序员并不完全以其拼写专业知识而闻名，并且将某些单词复数化是令人困惑的。例如，复数词是以“es”结尾还是只以“s”结尾？是人还是人？当您与大型团队一起处理项目时，这可能会成为一个问题。例如，团队成员使用不正确的方法将他创建的表复数的实例。当我与这个表交互时，它在我无权访问或需要很长时间修复的代码中到处使用。结果是我每次使用表格时都必须记住拼写错误。在我身上发生了与此非常相似的事情。您越容易让团队的每个成员始终如一地、轻松地使用准确的、正确的表名没有错误或必须一直查找表名，更好。在团队环境中，单一版本更容易处理。

2. 如果您使用表名的单数形式并在主键前加上表名，您现在可以通过代码轻松地从主键确定表名，反之亦然。您可以获得一个带有表名的变量，将“Id”连接到最后，现在您可以通过代码获得表的主键，而无需进行额外的查询。或者您可以从主键的末尾切断“Id”以通过代码确定表名。如果主键使用没有表名的“id”，则无法通过代码从主键确定表名。此外，大多数将表名复数并以表名作为前缀的 PK 列的人在 PK 中使用单数形式的表名（例如 statuses 和 status_id），

3. 如果您将表名设为单数，则可以让它们与它们所代表的类名相匹配。再一次，这可以简化代码并允许您做一些非常巧妙的事情，例如通过只有表名来实例化一个类。它还只是使您的代码更加一致，从而导致...

4. 如果您将表名设为单数，则可以使您的命名方案在每个位置保持一致、有条理且易于维护。您知道在代码中的每个实例中，无论是列名、类名还是表名，都是完全相同的名称。这允许您进行全局搜索以查看使用数据的所有地方。将表名复数化时，有时会使用该表名的单数形式（它变成的类，在主键中）。在某些情况下，您的数据被称为复数，而某些情况下是单数，这才有意义。

To sum it up, if you pluralize your table names you are losing all sorts of advantages in making your code smarter and easier to handle. There may even be cases where you have to have lookup tables/arrays to convert your table names to object or local code names you could have avoided. Singular table names, though perhaps feeling a little weird at first, offer significant advantages over pluralized names and I believe are best practice.

总而言之，如果您将表名复数化，您将失去使代码更智能、更易于处理的各种优势。甚至在某些情况下，您必须使用查找表/数组才能将表名转换为您可以避免的对象或本地代码名。单数表名虽然一开始可能感觉有点奇怪，但与复数名相比具有显着的优势，我相信这是最佳实践。

回答by Albert

our preference:

我们的偏好：

1. Should table names be plural?
   Never. The arguments for it being a collection make sense, but you never know what the table is going to contain (0,1 or many items). Plural rules make the naming unnecessarily complicated. 1 House, 2 houses, mouse vs mice, person vs people, and we haven't even looked at any other languages.

   Update person set property = 'value'acts on each person in the table.
   Select * from person where person.name = 'Greg'returns a collection/rowset of person rows.

2. Should column names be singular?
   Usually, yes, except where you are breaking normalisation rules.

3. Should I prefix tables or columns?
   Mostly a platform preference. We prefer to prefix columns with the table name. We don't prefix tables, but we do prefix views (v_) and stored_procedures (sp_ or f_ (function)). That helps people who want to try to upday v_person.age which is actually a calculated field in a view (which can't be UPDATEd anyway).

   It is also a great way to avoid keyword collision (delivery.from breaks, but delivery_from does not).

   It does make the code more verbose, but often aids in readability.

   bob = new person()
bob.person_name = 'Bob'
bob.person_dob = '1958-12-21'
   ... is very readable and explicit. This can get out of hand though:

   customer.customer_customer_type_id

   indicates a relationship between customer and the customer_type table, indicates the primary key on the customer_type table (customer_type_id) and if you ever see 'customer_customer_type_id' whilst debugging a query, you know instantly where it is from (customer table).

   or where you have a M-M relationship between customer_type and customer_category (only certain types are available to certain categories)

   customer_category_customer_type_id

   ... is a little (!) on the long side.

4. Should I use any case in naming items? Yes - lower case :), with underscores. These are very readable and cross platform. Together with 3 above it also makes sense.

   Most of these are preferences though. - As long as you are consistent, it should be predictable for anyone that has to read it.

1. 表名应该是复数吗？
   绝不。它是一个集合的论点是有道理的，但你永远不知道表将包含什么（0,1 或许多项目）。多个规则使命名变得不必要地复杂。1 房子，2 房子，老鼠对老鼠，人对人，我们甚至没有看过任何其他语言。

   Update person set property = 'value'作用于表中的每个人。
   Select * from person where person.name = 'Greg'返回人员行的集合/行集。

2. 列名应该是单数吗？
   通常，是的，除非您违反了规范化规则。

3. 我应该为表或列添加前缀吗？
   主要是平台偏好。我们更喜欢用表名作为列的前缀。我们不为表添加前缀，但我们为视图（v_）和存储过程（sp_ 或 f_（函数））添加前缀。这有助于那些想要尝试更新 v_person.age 的人，它实际上是视图中的一个计算字段（无论如何都不能更新）。

   这也是避免关键字冲突的好方法（delivery.from 中断，但 delivery_from 没有）。

   它确实使代码更加冗长，但通常有助于提高可读性。

   bob = new person()
bob.person_name = 'Bob'
bob.person_dob = '1958-12-21'
   ...非常易读和明确。不过，这可能会失控：

   customer.customer_customer_type_id

   表示 customer 和 customer_type 表之间的关系，表示 customer_type 表上的主键 (customer_type_id)，如果您在调试查询时看到“customer_customer_type_id”，您会立即知道它来自哪里（customer 表）。

   或者在 customer_type 和 customer_category 之间有 MM 关系（只有某些类型可用于某些类别）

   customer_category_customer_type_id

   ... 有点长（！）。

4. 我应该在命名项目时使用任何大小写吗？是 - 小写 :)，带下划线。这些是非常可读和跨平台的。再加上上面的 3 条也是有道理的。

   不过，其中大部分都是偏好。- 只要你是一致的，对于任何必须阅读它的人来说，它应该是可以预测的。

回答by SQLMenace

Take a look at ISO 11179-5: Naming and identification principles You can get it here: http://metadata-standards.org/11179/#11179-5

看看 ISO 11179-5：命名和识别原则你可以在这里得到它：http://metadata-standards.org/11179/#11179-5

I blogged about it a while back here: ISO-11179 Naming Conventions

我在博客上写了一段时间：ISO-11179 Naming Conventions

回答by Keith

My opinions on these are:

我对这些的看法是：

1) No, table names should be singular.

1) 不，表名应该是单数。

While it appears to make sense for the simple selection (select * from Orders) it makes less sense for the OO equivalent (Orders x = new Orders).

虽然它对于简单的选择 ( select * from Orders) 似乎有意义，但对于 OO 等效项 ( Orders x = new Orders)意义不大。

A table in a DB is really the set of that entity, it makes more sense once you're using set-logic:

数据库中的表实际上是该实体的集合，一旦您使用集合逻辑，它就更有意义：

select Orders.*
from Orders inner join Products
    on Orders.Key = Products.Key

That last line, the actual logic of the join, looks confusing with plural table names.

最后一行，连接的实际逻辑，看起来与复数表名混淆。

I'm not sure about always using an alias (as Matt suggests) clears that up.

我不确定是否总是使用别名（如马特建议的那样）来解决这个问题。

2) They should be singular as they only hold 1 property

2) 他们应该是单数，因为他们只持有 1 个财产

3) Never, if the column name is ambiguous (as above where they both have a column called [Key]) the name of the table (or its alias) can distinguish them well enough. You want queries to be quick to type and simple - prefixes add unnecessary complexity.

3) 永远不会，如果列名不明确（如上面它们都有一个名为 [Key] 的列），则表名（或其别名）可以很好地区分它们。您希望查询能够快速输入且简单 - 前缀会增加不必要的复杂性。

4) Whatever you want, I'd suggest CapitalCase

4）无论你想要什么，我都建议CapitalCase

I don't think there's one set of absolute guidelines on any of these.

我不认为有一套绝对的指导方针。

As long as whatever you pick is consistent across the application or DB I don't think it really matters.

只要您选择的任何内容在应用程序或数据库中都是一致的，我认为这并不重要。