数据库、表和列命名约定?

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. 表名应该是复数吗?
3. Should column names be singular?
4. 列名是单数吗?
5. Should I prefix tables or columns?
6. 我应该给表或列加上前缀吗?
7. Should I use any case in naming items?
8. 我应该在命名项目中使用任何案例吗?

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

在数据库中是否存在命名项的推荐准则?

23 个解决方案

#1

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

我建议检查Microsoft的SQL Server sample数据库: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. 奇异的名称表
3. Singular names for columns
4. 奇异的名字列
5. Schema name for tables prefix (E.g.: SchemeName.TableName)
6. 表前缀的模式名称(例如:SchemeName.TableName)
7. Pascal casing (a.k.a. upper camel case)
8. Pascal外壳(a.k.a upper camel case)

#2

Late answer here, but in short:

答案很晚，但简而言之:

1. My preference is plural
2. 我的偏好是复数
3. Yes
4. 是的
5. Tables: *Usually* no prefixes is best. Columns: No.
6. 表格:*通常没有前缀是最好的。列:没有。
7. Both tables and columns: Pascal casing.
8. 表和列:Pascal外壳。

Elaboration:

细化:

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

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

• Name your primary keys using "[singularOfTableName]ID" format. That is, whether your table name is Customer or Customers, the primary key should be CustomerID.
• 使用“[singularOfTableName]ID”格式指定主键。也就是说，无论您的表名是客户还是客户，主键都应该是CustomerID。
• Further, foreign keys must be named consistently in 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 often important, consistent foreign key naming is always important
• 此外，必须在不同的表中一致地指定外键。殴打不这样做的人应该是合法的。我认为，定义外键约束通常是重要的，一致的外键命名总是很重要的。
• You database must have internal conventions. Even though in later sections you'll see me being very flexible, within a database naming must be very consistent . Whether your table for customers is called Customers or Customer is 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.
• 数据库必须有内部约定。即使在后面的部分中，您将看到我非常灵活，在数据库命名中必须非常一致。无论您的客户表是客户还是客户，都不如您在相同的数据库中使用相同的方法。你可以抛硬币决定如何使用下划线，但是你必须用同样的方法。如果你不这样做，你就是一个不应该自卑的坏人。

(2) What you should probably do.

你应该做些什么。

• Fields representing the same kind of data on different tables should be named the same. Don't have Zip on one table and ZipCode on another.
• 在不同的表上表示相同类型数据的字段应该被命名为相同的。不要在一个表上有Zip，而在另一个表上有ZipCode。
• 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.)
• 要在表或列名中分隔单词，请使用pascalframe。使用camelframe并不是本质上的问题，但这不是惯例，而且看起来很有趣。稍后我将介绍下划线。(你不能像以前那样使用ALLCAPS。OBNOXIOUSTABLE。在DB2 20年前，注释_column是可以的，但现在不行。
• 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?
• 不要把单词写得太短或太短。一个名字要长而清晰，要比短而混乱好。超短的名字是一种从更黑暗、更野蛮的时代遗留下来的东西。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 Promotions and an Items table, 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.
• 使用强调一致和特定的目的。只是一般的表名称应该足够清楚地使用pascal外壳;你不需要用下划线来区分单词。保存下划线(a)表示关联表或(b)用于前缀，我将在下一颗子弹中说明。
• Prefixing is neither good or bad. It usually is 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 harder to 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.
• 前缀既不是好也不是坏。这通常不是最好的。在您的第一个或两个数据库中，我不建议使用前缀来进行常规的主题分组。表格最终不能很容易地适应你的分类，而且它实际上会使查找表变得更加困难。有了经验，你可以计划并应用一个比伤害更有益的预修方案。我曾在数据库中工作过，数据表以tbl、配置表和ctbl、vew、proc的sp和udf的fn以及其他几个数据表开始。它是精心设计的，始终如一地应用，所以它是可行的。你需要前缀的唯一时间是当你有真正独立的解决方案时，因为某种原因，存在于同一个数据库中;在对表进行分组时，前缀可以非常有用。在特殊情况下，前缀也是可以的，比如您想要突出的临时表。
• Very seldom (if ever) would you want to prefix columns.
• 很少(如果有的话)想要前缀列。

#3

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).

我认为表名应该是复数。表是实体的集合(表)。每一行表示一个实体，表代表集合。所以我把一个人的桌子叫做人(或人，不管你喜欢什么)。

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的“从人到人的选择”。

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

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

#4

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. 任何命名标准都比没有标准好。
3. There is no "one true" standard, we all have our preferences
4. 没有一个真正的标准，我们都有自己的偏好。
5. If there is standard already in place, use it. Don't create another standard or muddy the existing standards.
6. 如果已经有了标准，就使用它。不要创建另一个标准或混淆现有的标准。

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为“name”，_nb为“number”，_dt为“日期”)。

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

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

i.e.

即。

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.

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

#5

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. 不。表应该以它所代表的实体命名。人，而不是人是指你如何称呼任何一个记录代表的人。
3. Again, same thing. The column FirstName really should not be called FirstNames. It all depends on what you want to represent with the column.
4. 再一次,同样的事情。列FirstName确实不应该被称为FirstName。这完全取决于你想用这个列来表示什么。
5. NO.
6. 不。
7. Yes. Case it for clarity. If you need to have columns like "FirstName", casing will make it easier to read.
8. 是的。为了清晰。如果需要像“FirstName”这样的列，那么外壳将使其更易于阅读。

Ok. Thats my $0.02

好的。这是我0.02美元

#6

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:

参见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."

表是实体的集合，并遵循集合命名准则。理想情况下，使用一个集合名称:例如。、人员。复数也是正确的:员工。不正确的名字包括:雇员、雇员、雇员。

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.

与往常一样，规则也有例外，例如，一个总是只有一行的表可能会有一个单独的名称，例如一个配置表。一致性是至关重要的:检查你的商店是否有一个惯例，如果有，遵循它;如果你不喜欢它，那么就做一个商业案例，让它改变，而不是成为独行侠。

#7

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.

   表名应该是复数吗?从来没有。它作为集合的参数是有意义的，但是您永远不知道表将包含什么(0,1或许多项)。复数规则使命名变得不必要地复杂。1幢房子，2间房子，老鼠和老鼠，人vs人，我们甚至没有看其他语言。

   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.

   更新person set属性= 'value'作用于表中的每个人。从person.name = 'Greg'返回一个集合/行集的person行。

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).

   我应该给表或列加上前缀吗?主要是一个偏好的平台。我们更喜欢使用表名的列前缀。我们没有前缀表，但是我们使用前缀视图(v_)和stored_procedures (sp_或f_(函数))。这可以帮助那些想要尝试的人。age实际上是一个视图中的计算字段(无论如何都不能更新)。

   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:

   bob = new person() bob。person_name =“鲍勃”鲍勃。person_dob = ' 1958-12-21 '……非常清晰易读。这可能会失控:

   customer.customer_customer_type_id

   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).

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

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

   或者在customer_type和customer_category之间有一个M-M关系(只有某些类型可用于某些类别)

   customer_category_customer_type_id

   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.

   我应该在命名项目中使用任何案例吗?是的，小写字母:)，下划线。这些都是非常可读的跨平台。加上上面的3，这也是有意义的。

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

   这些都是首选项。-只要你是一致的，它就应该是可以预见的，任何人都必须读它。

#8

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命名约定。

#9

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 to 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.

   它避免了由复数歧义引起的错误和错误。程序员并不以他们的拼写技能而出名，而对一些单词进行多元化则会让人感到困惑。例如，复数词的结尾是es还是s ?是人还是人?当您与大型团队一起工作时，这可能成为一个问题。例如，一个团队成员使用不正确的方法来实现他创建的表的复数。当我与这个表交互时，它在我无法访问或需要花费很长时间修复的代码中被使用。结果是我必须记住在每次使用表格时拼写错误。类似的事情发生在我身上。您可以更容易地使团队中的每个成员一致地、轻松地使用正确的、正确的表名，而不会出现错误，或者不得不一直查找表名，这样就更好了。在团队环境中，单数版本更容易处理。

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 statusId), making it impossible to do this at all.

   如果您使用表名的单数版本，并使用表名的主键前缀，那么您现在可以轻松地从主键中确定表名，反之亦然。可以给您一个带有表名的变量，将“Id”连接到最后，现在您可以通过代码获得该表的主键，而不必执行额外的查询。或者，您可以从主键的末尾删除“Id”，以通过代码确定表名。如果您在主键没有表名的情况下使用“id”，那么您就不能通过代码从主键确定表名。此外，大多数人将表名和前缀PK列成复数，在PK中使用表名的单数版本(例如statuse和sid)，因此根本不可能做到这一点。

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.

   如果您将表命名为单数，它将使您的命名方案一致、有组织且易于维护。您知道在代码中的每个实例中，无论是列名称、类名还是表名，都是相同的名称。这允许您进行全局搜索，以查看所使用的所有数据。当您对一个表名进行复数时，将会出现一些情况，其中您将使用该表名的单数版本(在主键中，它会转换成这个类)。在某些情况下，你的数据被称为复数，有些实例是单数，这是有道理的。

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.

总结一下，如果您将您的表命名为复数，那么您将失去使您的代码更智能、更容易处理的各种优势。甚至有可能需要查找表/数组来将表名转换为对象或可以避免的本地代码名。单数的表名，虽然一开始可能有点奇怪，但它比复数的名字有显著的优势，我认为这是最好的做法。

#10

I know this is late to the game, and the question has been answered very well already, but I want to offer my opinion on #3 regarding the prefixing of column names.

我知道这已经很晚了，这个问题已经回答得很好了，但是我想在#3上给出我的观点，关于列名的前缀。

All columns should be named with a prefix that is unique to the table they are defined in.

所有列都应该用一个前缀命名，这个前缀是定义在表中的唯一的。

E.g. Given tables "customer" and "address", let's go with prefixes of "cust" and "addr", respectively. "customer" would have "cust_id", "cust_name", etc. in it. "address" would have "addr_id", "addr_cust_id" (FK back to customer), "addr_street", etc. in it.

例:给定表“客户”和“地址”，让我们分别用“cust”和“addr”的前缀。“客户”将有“cust_id”、“cust_name”等。“地址”将有“addr_id”、“addr_cust_id”(FK back to customer)、“addr_street”等。

When I was first presented with this standard, I was dead-set against it; I hated the idea. I couldn't stand the idea of all that extra typing and redundancy. Now I've had enough experience with it that I'd never go back.

当我第一次提出这个标准的时候，我坚决反对它;我讨厌这个想法。我无法忍受额外的打字和冗余。现在我已经有了足够的经验，我再也不会回去了。

The result of doing this is that all of the columns in your database schema are unique. There is one major benefit to this, which trumps all arguments against it (in my opinion, of course):

这样做的结果是，数据库模式中的所有列都是惟一的。这其中有一个重要的好处，它战胜了所有反对它的理由(当然，在我看来):

You can search your entire code base and reliably find every line of code that touches a particular column.

您可以搜索整个代码库并可靠地找到涉及特定列的每一行代码。

The benefit from #1 is incredibly huge. I can deprecate a column and know exactly what files need to be updated before the column can safely be removed from the schema. I can change the meaning of a column and know exactly what code needs to be refactored. Or I can simply tell if data from a column is even being used in a particular portion of the system. I can't count the number of times this has turned a potentially huge project into a simple one, nor the amount of hours we've saved in development work.

1的好处是巨大的。我可以反对一个列，并且知道在该列可以安全地从模式中删除之前需要更新哪些文件。我可以更改一个列的含义，并确切地知道需要重构什么代码。或者，我可以简单地判断一个列中的数据是否在系统的特定部分中使用。我数不清这已经把一个潜在的巨大项目变成了一个简单的项目，也不能计算我们在开发工作中节省的时间。

Another, relatively minor benefit to it is that you only have to use table-aliases when you do a self join:

另一个相对较小的好处是，当你进行自我连接时，你只需要使用表格-别名:

SELECT cust_id, cust_name, addr_street, addr_city, addr_state
    FROM customer
        INNER JOIN address ON addr_cust_id = cust_id
    WHERE cust_name LIKE 'J%';

#11

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).

虽然对于简单的选择(从订单中选择*)似乎是有意义的，但是对于OO(订单x =新订单)来说，它的意义更小。

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

DB中的表实际上是那个实体的集合，当你使用集合逻辑时，它更有意义:

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.

我不确定是否总是使用别名(如Matt所建议的)清除这些。

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.

如果列名称不明确(如上所示，它们都有一个名为[Key]的列)，那么表的名称(或别名)可以很好地区分它们。您希望查询能够快速的键入和简单的前缀添加不必要的复杂性。

4) Whatever you want, I'd suggest 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.

只要你选择的东西在应用程序或数据库中是一致的，我认为这并不重要。

#12

In my opinion:

在我看来:

1. Table names should be plural.
2. 表名应该是复数。
3. Column names should be singular.
4. 列名应该是单数。
5. No.
6. 不。
7. Either CamelCase (my preferred) or underscore_separated for both table names and column names.
8. 要么是CamelCase(我喜欢的)，要么是两个表名和列名的underscore_分开。

However, like it has been mentioned, any convention is better than no convention. No matter how you choose to do it, document it so that future modifications follow the same conventions.

然而，就像前面提到的那样，任何公约都比没有公约要好。无论您如何选择，都要记录它，以便将来的修改遵循相同的约定。

#13

1. Definitely keep table names singular, person not people
   2. Same here
   3. 我也一样
   4. No. I've seen some terrible prefixes, going so far as to state what were dealing with is a table (tbl_) or a user store procedure (usp_). This followed by the database name... Don't do it!
   5. 不。我已经看到了一些可怕的前缀，到目前为止，要说明处理的是一个表(tbl_)或用户存储过程(usp_)。接下来是数据库名称…不要这样做!
   6. Yes. I tend to PascalCase all my table names
   7. 是的。我倾向于把我所有的表名都写出来。
2. 一定要保留表名单数，这里的人不一样。我已经看到了一些可怕的前缀，到目前为止，要说明处理的是一个表(tbl_)或用户存储过程(usp_)。接下来是数据库名称…不要这样做!是的。我倾向于把我所有的表名都写出来。

#14

I think the best answer to each of those questions would be given by you and your team. It's far more important to have a naming convention then how exactly the naming convention is.

我认为对这些问题最好的回答将由你和你的团队给出。命名约定更重要的是命名约定是怎样的。

As there's no right answer to that, you should take some time (but not too much) and choose your own conventions and - here's the important part - stick to it.

因为没有正确的答案，你应该花点时间(但不要太多)，选择自己的习惯，这是最重要的部分。

Of course it's good to seek some information about standards on that, which is what you're asking, but don't get anxious or worried about the number of different answers you might get: choose the one that seems better for you.

当然，寻求一些关于标准的信息是很好的，这是你要问的，但是不要担心或者担心你可能得到的不同答案的数量:选择一个对你来说更好的答案。

Just in case, here are my answers:

以防万一，以下是我的答案:

1. Yes. A table is a group of records, teachers or actors, so... plural.
2. 是的。桌子是一组记录，老师或演员，所以……复数。
3. Yes.
4. 是的。
5. I don't use them.
6. 我不使用它们。
7. The database I use more often - Firebird - keeps everything in upper case, so it doesn't matter. Anyway, when I'm programming I write the names in a way that it's easier to read, like releaseYear.
8. 我经常使用的数据库——Firebird——把所有东西都放在大写，所以没关系。不管怎样，当我在编程的时候，我用一种更容易阅读的方式来写名字，比如发布年份。

#15

Naming conventions allow the development team to design discovereability and maintainability at the heart of the project.

命名约定允许开发团队在项目的核心设计可发现性和可维护性。

A good naming convention takes time to evolve but once it’s in place it allows the team to move forward with a common language. A good naming convention grows organically with the project. A good naming convention easily copes with changes during the longest and most important phase of the software lifecycle - service management in production.

好的命名约定需要时间来发展，但是一旦它就位，它就允许团队以一种通用的语言前进。一个好的命名约定与项目有机地增长。一个好的命名约定很容易在软件生命周期的最长和最重要的阶段——即生产中的服务管理——进行更改。

Here are my answers:

以下是我的回答:

1. Yes, table names should be plural when they refer to a set of trades, securities, or counterparties for example.
2. 是的，当表名涉及一套交易、证券或交易对手时，应该是复数。
3. Yes.
4. 是的。
5. Yes. SQL tables are prefixed with tb_, views are prefixed vw_, stored procedures are prefixed usp_ and triggers are prefixed tg_ followed by the database name.
6. 是的。SQL表用tb_前缀，视图是前缀vw_，存储过程是前缀usp_，触发器是前缀tg_，后面是数据库名。
7. Column name should be lower case separated by underscore.
8. 列名应该用下划线隔开。

Naming is hard but in every organisation there is someone who can name things and in every software team there should be someone who takes responsibility for namings standards and ensures that naming issues like sec_id, sec_value and security_id get resolved early before they get baked into the project.

命名是困难的，但是在每个组织中都有一个人可以命名事物，在每个软件团队中，应该有一个人负责命名标准，并确保像sec_id、sec_value和security_id这样的命名问题在他们被纳入项目之前就得到了解决。

So what are the basic tenets of a good naming convention and standards: -

好的命名规范和标准的基本原则是什么?

• Use the language of your client and your solution domain
• 使用客户端和解决方案域的语言。
• Be descriptive
• 是描述性的
• Be consistent
• 是一致的
• Disambiguate, reflect and refactor
• 消除歧义,体现和重构
• Don’t use abbreviations unless they are clear to everyone
• 不要使用缩写，除非每个人都清楚。
• Don’t use SQL reserved keywords as column names
• 不要将SQL保留关键字作为列名。

#16

Here's a link that offers a few choices. I was searching for a simple spec I could follow rather than having to rely on a partially defined one.

这里有一个提供一些选择的链接。我正在寻找一个简单的规范，而不是依赖于一个部分定义的规范。

http://justinsomnia.org/writings/naming_conventions.html

http://justinsomnia.org/writings/naming_conventions.html

#17

Table names should always be singular, because they represent a set of objects. As you say herd to designate a group of sheep, or flock do designate a group of birds. No need for plural. When a table name is composition of two names and naming convention is in plural it becomes hard to know if the plural name should be the first word or second word or both. It’s the logic – Object.instance, not objects.instance. Or TableName.column, not TableNames.column(s). Microsoft SQL is not case sensitive, it’s easier to read table names, if upper case letters are used, to separate table or column names when they are composed of two or more names.

表名应该总是单数，因为它们表示一组对象。正如你所说的牧群指定一群绵羊，或羊群指定一群鸟。不需要复数。当一个表名是两个名称的组合，命名约定是复数时，很难知道复数名称应该是第一个单词还是第二个单词，或者两者都是。它是逻辑对象。实例,而不是objects.instance。或表。列,而不是TableNames.column(年代)。Microsoft SQL不是区分大小写的，如果使用大写字母，则更容易读取表名，当它们由两个或多个名称组成时，可以将表或列名分开。

#18

Table Name: It should be singular, as it is a singular entity representing a real world object and not objects, which is singlular.

表名:它应该是奇异的，因为它是一个单独的实体，它代表一个真实的世界对象而不是对象，它是singlular。

Column Name: It should be singular only then it conveys that it will hold an atomic value and will confirm to the normalization theory. If however, there are n number of same type of properties, then they should be suffixed with 1, 2, ..., n, etc.

列名:它应该是单数的，它表示它将持有一个原子值，并将确认归一化理论。但是，如果有n个相同类型的属性，那么它们应该以1、2、……、n等。

Prefixing Tables / Columns: It is a huge topic, will discuss later.

预定表/列:这是一个很大的话题，稍后将讨论。

Casing: It should be Camel case

肠衣:应该是驼背。

My friend, Patrick Karcher, I request you to please not write anything which may be offensive to somebody, as you wrote, "•Further, foreign keys must be named consistently in different tables. It should be legal to beat up someone who does not do this.". I have never done this mistake my friend Patrick, but I am writing generally. What if they together plan to beat you for this? :)

我的朋友，Patrick Karcher，我请求你不要写任何可能冒犯别人的东西，正如你所写的那样，“进一步，外键必须在不同的表中一致地命名。殴打不这样做的人应该是合法的。我从来没有犯过这样的错误，我的朋友帕特里克，但我通常是在写作。如果他们一起计划打你呢?:)

#19

Very late to the party but I still wanted to add my two cents about column prefixes

聚会很晚了，但我还是想把我的两美分加起来。

There seem to be two main arguments for using the table_column (or tableColumn) naming standard for columns, both based on the fact that the column name itself will be unique across your whole database:

使用table_column(或tableColumn)的列命名标准似乎有两个主要的参数，这两个参数都基于一个事实:列名称本身在整个数据库中是惟一的:

1) You do not have to specify table names and/or column aliases in your queries all the time

1)您不必在查询中始终指定表名和/或列别名。

2) You can easily search your whole code for the column name

2)您可以轻松地搜索整个代码的列名。

I think both arguments are flawed. The solution for both problems without using prefixes is easy. Here's my proposal:

我认为这两种观点都有缺陷。解决两个问题而不使用前缀很容易。这是我的建议:

Always use the table name in your SQL. E.g., always use table.column instead of column.

总是在SQL中使用表名。例如,总是用表。列的列。

It obviously solves 2) as you can now just search for table.column instead of table_column.

它显然解决了2)你现在可以搜索表格了。而不是table_column列。

But I can hear you scream, how does it solve 1)? It was exactly about avoiding this. Yes, it was, but the solution was horribly flawed. Why? Well, the prefix solution boils down to:
To avoid having to specify table.column when there's ambiguity, you name all your columns table_column!
But this means you will from now on ALWAYS have to write the column name every time you specify a column. But if you have to do that anyways, what's the benefit over always explicitly writing table.column? Exactly, there is no benefit, it's the exact same number of characters to type.

但我能听到你尖叫，它是如何解决的?这完全是为了避免这种情况。是的，的确如此，但解决方案存在严重缺陷。为什么?嗯，前缀解决方案可归结为:避免必须指定表。当有歧义时，您可以命名所有列table_column!但这意味着，从现在开始，每次指定列时，都必须写列名。但是，如果你必须这样做，什么好处总是显式写表。列?确切地说，没有任何好处，它是相同数量的字符类型。

edit: yes, I am aware that naming the columns with the prefix enforces the correct usage whereas my approach relies on the programmers

编辑:是的，我知道用前缀命名这些列会强制正确使用，而我的方法依赖于程序员。

#20

Essential Database Naming Conventions (and Style) (click here for more detailed description)

基本的数据库命名约定(和样式)(单击此处查看更详细的描述)

table names choose short, unambiguous names, using no more than one or two words distinguish tables easily facilitates the naming of unique field names as well as lookup and linking tables give tables singular names, never plural (update: i still agree with the reasons given for this convention, but most people really like plural table names, so i’ve softened my stance)... follow the link above please

表名选择短的,明确的名字,使用不超过一个或两个词区分表容易促进独特的命名字段名称以及查找和联系表给表奇异的名字,从来没有复数(更新:我仍然同意本公约的原因,但大多数人真的喜欢复数表名,所以我软化了立场)……请按照上面的链接。

#21

SELECT 
   UserID, FirstName, MiddleInitial, LastName
FROM Users
ORDER BY Lastname

#22

Table names singular. Let's say you were modelling a realtionship between someone and their address. For example, if you are reading a datamodel would you prefer 'each person may live at 0,1 or many address.' or 'each people may live at 0,1 or many addresses.' I think its easier to pluralise address, rather than have to rephrase people as person. Plus collective nouns are quite often dissimlar to the singular version.

表名奇异。假设你正在模拟某人和他们的地址之间的关系。例如，如果您正在阅读一个datamodel，您是否希望“每个人可以在0、1或多个地址中生存”。或者“每个人可能生活在0、1或多个地址。”我认为，多元化的解决方案更容易实现，而不是必须重新措辞。加上集体名词通常是单数形式。

#23

--Example SQL

CREATE TABLE D001_Students
(
    StudentID INTEGER CONSTRAINT nnD001_STID NOT NULL,
    ChristianName NVARCHAR(255) CONSTRAINT nnD001_CHNA NOT NULL,
    Surname NVARCHAR(255) CONSTRAINT nnD001_SURN NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(StudentID)
);

CREATE INDEX idxD001_STID on D001_Students;

CREATE TABLE D002_Classes
(
    ClassID INTEGER CONSTRAINT nnD002_CLID NOT NULL,
    StudentID INTEGER CONSTRAINT nnD002_STID NOT NULL,
    ClassName NVARCHAR(255) CONSTRAINT nnD002_CLNA NOT NULL,
    CONSTRAINT pkD001 PRIMARY KEY(ClassID, StudentID),
    CONSTRAINT fkD001_STID FOREIGN KEY(StudentID) 
        REFERENCES D001_Students(StudentID)
);

CREATE INDEX idxD002_CLID on D002_Classes;

CREATE VIEW V001_StudentClasses
(
    SELECT
        D001.ChristianName,
        D001.Surname,
        D002.ClassName
    FROM
        D001_Students D001
            INNER JOIN
        D002_Classes D002
            ON
        D001.StudentID = D002.StudentID
);

These are the conventions I was taught, but you should adapt to whatever you developement hose uses.

这些是我教过的习惯，但是你应该适应你开发的软管使用的任何东西。

1. Plural. It is a collection of entities.
2. 复数。它是实体的集合。
3. Yes. The attribute is a representation of singular property of an entity.
4. 是的。属性是一个实体的奇异属性的表示。
5. Yes, prefix table name allows easily trackable naming of all constraints indexes and table aliases.
6. 是的，前缀表名称允许方便地命名所有约束索引和表别名。
7. Pascal Case for table and column names, prefix + ALL caps for indexes and constraints.
8. 用于表和列名的Pascal语言，前缀+所有索引和约束的大写。