database 关系表命名约定

Relational table naming convention

提问by Andreas

I'm starting a new project and would like to get my table- and column names right from the start. For example I've always used plural in table names but recently learned singular is correct.

我正在开始一个新项目，并希望从一开始就获得我的表名和列名。例如，我一直在表名中使用复数，但最近学到的单数是正确的。

So, if I got a table "user" and then I got products that only the user will have, should the table be named "user_product" or just "product" ? This is a one to many relationship.

因此，如果我有一个表“user”，然后我得到了只有用户才能拥有的产品，那么该表应该命名为“user_product”还是只是“product”？这是一对多的关系。

And further on, if i would have (for some reason) several product descriptions for each product, would it be "user_product_description" or "product_description" or just "description"? Of course with the right foreign keys set.. Naming it only description would be problematic since i could also have user description or account description or whatever..

更进一步，如果我（出于某种原因）每个产品都有几个产品描述，它是“user_product_description”还是“product_description”还是只是“description”？当然，使用正确的外键设置.. 仅命名它的描述会有问题，因为我也可以有用户描述或帐户描述或其他任何东西..

What about if i want a pure relational table (many to many) with only two columns, what would this look like? "user_stuff" or maybe something like "rel_user_stuff" ? And if the first one, what would distinguish this from, for example "user_product"?

如果我想要一个只有两列的纯关系表（多对多）怎么办，这会是什么样子？"user_stuff" 或者类似 "rel_user_stuff" 的东西？如果是第一个，它与例如“user_product”有何区别？

Any help is highly appreciated and if there is some sort of naming convention standard out there that you guys recommend, feel free to link.

任何帮助都非常感谢，如果你们推荐某种命名约定标准，请随时链接。

Thanks

谢谢

回答by PerformanceDBA

Table ? Name

桌子 ？姓名

recently learned singular is correct

最近学的单数是对的

Yes. Beware of the heathens. Plural in the table namesare a sure sign of someone who has not read any of the standard materials and has no knowledge of database theory.

是的。小心异教徒。表名中的复数表示某人没有阅读任何标准材料并且不了解数据库理论。

Some of the wonderful things about Standards are:

关于标准的一些奇妙之处是：

• they are all integrated with each other
• they work together
• they were written by minds greater than ours, so we do not have to debate them.

• 它们都相互融合
• 他们一起工作
• 它们是由比我们更伟大的思想写成的，所以我们不必辩论它们。

The standard table name refers to each rowin the table, which is used in the all verbiage, not the total content of the table (we know that the Customertable contains all the Customers).

标准表名是指表中的每一行，用在所有的措辞中，而不是表的总内容（我们知道该Customer表包含所有客户）。

Relationship, Verb Phrase

关系，动词短语

In genuine Relational Databases that have been modelled (as opposed to pre-1970's Record Filing Systems [characterised by Record IDswhich are implemented in an SQL database container for convenience):

在已建模的真正关系数据库中（与 1970 年之前的记录归档系统相反 [其特点Record IDs是为了方便起见在 SQL 数据库容器中实现）：

• the tables are the Subjectsof the database, thus they are nouns, again, singular
• the relationships between the tables are the Actionsthat take place between the nouns, thus they are verbs(i.e they are not arbitrarily numbered or named)
• that isthe Predicate
• all that can be read directly from the data model (refer my examples at the end)
• (the Predicate for an independent table (the top-most parent in an hierarchy) is that it is independent)
• thus the Verb Phraseis carefully chosen, so that it is the most meaningful, and generic terms are avoided (this becomes easier with experience). The Verb Phrase is important during modelling because it assists in resolving the model, ie. clarifying relations, identifying errors, and correcting the table names.

• 表是数据库的主题，因此它们是名词，再次，单数
• 表之间的关系是名词之间发生的动作，因此它们是动词（即它们不是任意编号或命名的）
• 这是该谓词
• 所有可以直接从数据模型中读取的内容（请参阅我最后的示例）
• （独立表（层次结构中最顶层的父级）的谓词是独立的）
• 因此，动词短语是经过仔细选择的，因此它是最有意义的，并且避免了通用术语（随着经验的增加，这变得更容易）。动词短语在建模过程中很重要，因为它有助于解析模型，即。澄清关系，识别错误，更正表名。

Diagram_A

图_A

Of course, the relationship is implemented in SQL as a CONSTRAINT FOREIGN KEYin the child table (more, later). Here is the Verb Phrase(in the model), the Predicatethat it represents (to be read from the model), and the FK Constraint Name:

当然，关系在 SQL 中实现为CONSTRAINT FOREIGN KEY子表中的 a（更多，稍后）。这是动词短语（在模型中）、它代表的谓词（从模型中读取）和 FK约束名称：

    Initiates
    Each Customer Initiates 0-to-n SalesOrders
    Customer_Initiates_SalesOrder_fk

Table ? Language

桌子 ？语

However, when describingthe table, particularly in technical language such as the Predicates, or other documentation, use singular and plurals as they naturally in the English language. Keeping in mind the table is named for the single row (relation) and the language refers to each derived row (derived relation):

但是，在描述表格时，尤其是使用技术语言（如谓词）或其他文档时，请使用单数和复数，因为它们在英语中很自然。请记住，该表是以单行（关系）命名的，并且该语言指的是每个派生行（派生关系）：

    Each Customer initiates zero-to-many SalesOrders

not

不是

    Customers have zero-to-many SalesOrders 

So, if I got a table "user" and then I got products that only the user will have, should the table be named "user-product" or just "product"? This is a one to many relationship.

那么，如果我有一个表“user”，然后我得到了只有用户才能拥有的产品，那么该表应该命名为“user-product”还是“product”？这是一对多的关系。

(That is not a naming-convention question; that is a a db design question.) It doesn't matter if user::productis 1::n. What matters is whether productis a separate entity and whether it is an Independent Table, ie. it can exist on its own. Therefore product, not user_product.

（这不是命名约定问题；那是数据库设计问题。）是否user::product为 1::n并不重要。重要的是是否product是一个单独的实体以及它是否是一个独立的表，即。它可以独立存在。因此product，不是user_product。

And if productexists only in the context of an user, ie. it is a Dependent Table, therefore user_product.

并且 ifproduct仅存在于 an 的上下文中user，即。它是一个相关表，因此user_product。

Diagram_B

图_B

And further on, if i would have (for some reason) several product descriptions for each product, would it be "user-product-description" or "product-description" or just "description"? Of course with the right foreign keys set.. Naming it only description would be problematic since i could also have user description or account description or whatever.

更进一步，如果我（出于某种原因）每个产品都有几个产品描述，它是“用户产品描述”还是“产品描述”或只是“描述”？当然，使用正确的外键设置.. 仅将其命名为描述会有问题，因为我还可以有用户描述或帐户描述或其他任何内容。

That's right. Either user_product_descriptionxor product_descriptionwill be correct, based on the above. It is not to differentiate it from other xxxx_descriptions, but it is to give the name a sense of where it belongs, the prefix being the parent table.

这是正确的。无论是user_product_description异或product_description将是正确的，基于以上。这不是为了将它与 other 区分开来xxxx_descriptions，而是为了让名称具有它所属的位置，前缀是父表。

What about if i want a pure relational table (many to many) with only two columns, what would this look like? "user-stuff" or maybe something like "rel-user-stuff" ? And if the first one, what would distinguish this from, for example "user-product"?

如果我想要一个只有两列的纯关系表（多对多）怎么办，这会是什么样子？“用户资料”或“rel-user-stuff”之类的东西？如果是第一个，这与“用户产品”有什么区别？

1. Hopefully all the tables in the relational database are pure relational, normalised tables. There is no need to identify that in the name (otherwise all the tables will be rel_something).

2. If it contains onlythe PKs of the two parents (which resolves the logicaln::n relationship that does not exist as an entity at the logical level, into a physicaltable), that is an Associative Table. Yes, typically the name is a combination of the two parent table names.

   • Note that is such cases the Verb Phrase applies to, and is read as, from parent to parent, ignoring the child table, because its only purpose in life is to relate the two parents.

     Diagram_C

   • If it is notan Associative Table (ie. in addition to the two PKs, it contains data), then name it appropriately, and the Verb Phrases apply to it, not the parent at the end of the relationship.

     Diagram_D

3. If you end up with two user_producttables, then that is a very loud signal that you have not normalised the data. So go back a few steps and do that, and name the tables accurately and consistently. The names will then resolve themselves.

1. 希望关系数据库中的所有表都是纯关系的、规范化的表。不需要在名称中标识它（否则所有表都将是rel_something）。

2. 如果它仅包含两个父级的 PK（将逻辑级别上不作为实体存在的逻辑n::n 关系解析为物理表），则为关联表。是的，通常名称是两个父表名称的组合。

   • 请注意，在这种情况下，动词短语适用于并被解读为从父母到父母，忽略子表，因为它在生活中的唯一目的是将两个父母联系起来。

     图_C

   • 如果它不是关联表（即，除了两个 PK 之外，它还包含数据），则对其进行适当命名，并且动词短语适用于它，而不是关系末尾的父项。

     图_D

3. 如果您最终得到两个user_product表，那么这是一个非常响亮的信号，表明您尚未对数据进行标准化。因此，返回几个步骤并执行此操作，并准确且一致地命名表。然后名称将自行解析。

Naming Convention

命名约定

Any help is highly appreciated and if there is some sort of naming convention standard out there that you guys recommend, feel free to link.

任何帮助都非常感谢，如果你们推荐某种命名约定标准，请随时链接。

What you are doing is very important, and it will affect the ease of use and understanding at every level. So it is good to get as much understanding as possible at the outset. The relevance of most of this will not be clear, until you start coding in SQL.

你在做什么很重要，它会影响到各个层面的易用性和理解。因此，最好在一开始就获得尽可能多的了解。在您开始用 SQL 编码之前，大部分内容的相关性都不会清楚。

1. Caseis the first item to address. All caps is unacceptable. Mixed case is normal, especially if the tables are directly accessible by the users. Refer my data models. Note that when the seeker is using some demented NonSQL, that has only lowercase, I give that, in which case I include underscores (as per your examples).

2. Maintain a data focus, not an application or usage focus. It is, after all 2011, we have had Open Architecturesince 1984, and databases are supposed to be independent of the apps that use them.

   That way, as they grow, and more than the one app uses them, the naming will remain meaningful, and need no correction. (Databases that are completely embedded in a single app are not databases.) Name the data elements as data, only.

3. Be very considerate, and name tables and columns very accurately. Do not use UpdatedDateif it is a DATETIMEdatatype, use UpdatedDtm. Do not use_descriptionif it contains a dosage.

4. It is important to be consistentacross the database. Do not use NumProductin one place to indicate number of Products and ItemNoor ItemNumin another place to indicate number of Items. Use NumSomethingfor numbers-of, and SomethingNoor SomethingIdfor identifiers, consistently.

5. Do not prefix the column name with a table name or short code, such as user_first_name. SQL already provides for the tablename as a qualifier:

       table_name.column_name  -- notice the dot
   

6. Exceptions:

   • The first exception is for PKs, they need special handling because you code them in joins, all the time, and you want keys to stand out from data columns. Always use user_id, never id.

     • Note that this is nota table name used as a prefix, but a proper descriptive name for the component of the key: user_idis the column that identifies an user, not the idof the usertable.
       • (Except of course in record filing systems, where the files are accessed by surrogates and there are no relational keys, there they are one and the same thing).
     • Always use the exact same name for the key column wherever the PK is carried (migrated) as an FK.
     • Therefore the user_producttable will have an user_idas a component of its PK (user_id, product_no).
     • the relevance of this will become clear when you start coding. First, with an idon many tables, it is easy get mixed up in SQL coding. Second, anyone other that the initial coder has no idea what he was trying to do. Both of which are easy to prevent, if the key columns are treated as above.

   • The second exception is where there is more than one FK referencing the same parent table table, carried in the child. As per the Relational Model, use Role Namesto differentiate the meaning or usage, eg. AssemblyCodeand ComponentCodefor two PartCodes. And in that case, do notuse the undifferentiated PartCodefor one of them. Be precise.

     Diagram_E

7. Prefix
   Where you have more than say 100 tables, prefix the table names with a Subject Area:

   REF_for Reference tables
   OE_for the Order Entry cluster, etc.

   Only at the physical level, not the logical (it clutters the model).

8. Suffix
   Never use suffixes on tables, and always use suffixes on everything else. That means in the logical, normal use of the database, there are no underscores; but on the administrative side, underscores are used as a separator:

   _VView (with the main TableNamein front, of course)
   _fkForeign Key (the constraint name, not the column name)
   _cacCache
   _segSegment
   _trTransaction (stored proc or function)
   _fnFunction (non-transactional), etc.

   The format is the table or FK name, an underscore, and action name, an underscore, and finally the suffix.

   This is really important because when the server gives you an error message:

   ____blah blah blah error on object_name

   you know exactly what object was violated, and what it was trying to do:

   ____blah blah blah error on Customer_Add_tr

9. Foreign Keys(the constraint, not the column). The best naming for a FK is to use the Verb Phrase (minus the "each" and the cardinality).

   Customer_Initiates_SalesOrder_fk
Part_Comprises_Component_fk
Part_IsConsumedIn_Assembly_fk

   Use the Parent_Child_fksequence, not Child_Parent_fkis because (a) it shows up in the correct sort order when you are looking for them and (b) we always know the child involved, what we are guessing at is, which parent. The error message is then delightful:

   ____Foreign key violation on Vendor_Offers_PartVendor_fk.

   That works well for people who bother to model their data, where the Verb Phrases have been identified. For the rest, the record filing systems, etc, use Parent_Child_fk.

10. Indices are special, so they have a naming convention of their very own, made up of, in order, each character position from 1 to 3:

    UUnique, or _for non-unique
    CClustered, or _for non-clustered
    _separator

    For the remainder:

    • If the key is one column or a very few columns:
      ____ColumnNames

    • If the key is more than a few columns:
      ____PKPrimary Key (as per model)
      ____AK[*n*]Alternate Key (IDEF1X term)

    Note that the table name is notrequired in the index name, because it always shows up as table_name.index_name.

    So when Customer.UC_CustomerIdor Product.U__AKappears in an error message, it tells you something meaningful. When you look at the indices on a table, you can differentiate them easily.

11. Find someone qualified and professional and follow them. Look at their designs, and carefully study the naming conventions they use. Ask them specific questions about anything you do not understand. Conversely, run like hell from anyone who demonstrates little regard for naming conventions or standards. Here's a few to get you started:

    • They contain real examples of all the above. Ask questions re naming questions in this thread.
    • Of course, the models implement several otherStandards, beyond naming conventions; you can either ignore those for now, or feel free to ask specific new questions.
    • They are several pages each, inline image support at Stack Overflow is for the birds, and they do not load consistently on different browsers; so you will have to click the links.
    • Note that PDF files have full navigation, so click on the blue glass buttons, or the objects where expansion is identified:
    • Readers who are unfamiliar with the Relational Modelling Standard may find the IDEF1X Notationhelpful.

1. 案例是要解决的第一个项目。所有大写都是不可接受的。混合大小写是正常的，尤其是当用户可以直接访问表时。参考我的数据模型。请注意，当搜索者使用一些只有小写的疯狂 NonSQL 时，我会给出，在这种情况下，我会包含下划线（根据您的示例）。

2. 保持数据焦点，而不是应用程序或使用焦点。毕竟是 2011 年，我们从 1984 年开始就有开放架构，数据库应该独立于使用它们的应用程序。

   这样，随着它们的增长，并且不止一个应用程序使用它们，命名将保持有意义，不需要更正。（完全嵌入单个应用程序中的数据库不是数据库。）仅将数据元素命名为数据。

3. 非常体贴，非常准确地命名表和列。UpdatedDate如果它是DATETIME数据类型，请不要使用，请使用UpdatedDtm. _description如果含有剂量，请勿使用。

4. 在整个数据库中保持一致很重要。不要NumProduct在一个地方使用来表示产品的数量，ItemNo或者ItemNum在另一个地方使用来表示项目的数量。使用NumSomething了数字-，并SomethingNo或SomethingId标识符，始终如一。

5. 不要在列名前加上表名或短代码，例如user_first_name. SQL 已经提供了表名作为限定符：

       table_name.column_name  -- notice the dot
   

6. 例外：

   • 第一个例外是 PK，它们需要特殊处理，因为您一直在连接中对它们进行编码，并且您希望键从数据列中脱颖而出。总是使用user_id，从不id。

     • 注意，这不是用作前缀的表名，但对于密钥的部件适当的描述性名称：user_id是列是标识用户，而不是id所述的user表中。
       • （当然，除了在记录归档系统中，文件由代理访问并且没有关系键，它们是一回事）。
     • 在 PK 作为 FK 携带（迁移）的任何地方，始终对键列使用完全相同的名称。
     • 因此，该user_product表将user_id作为其 PK 的一个组件(user_id, product_no)。
     • 当您开始编码时，这一点的相关性将变得清晰。首先，id对于许多表，很容易在 SQL 编码中混淆。其次，除了最初的编码员之外的任何人都不知道他想做什么。如果关键列按上述处理，这两种情况都很容易防止。

   • 第二个例外是有多个 FK 引用同一个父表，在子表中携带。根据关系模型，使用角色名称来区分含义或用法，例如。AssemblyCode和ComponentCode两个PartCodes。而在这种情况下，千万不能使用未分化PartCode的其中之一。要准确。

     图_E

7. 前缀
   如果您有超过 100 个表，请在表名前加上主题区域：

   REF_用于
   OE_订单输入集群的参考表等。

   仅在物理级别，而不是逻辑级别（它使模型混乱）。

8. 后缀
   永远不要在表格上使用后缀，并且总是在其他所有东西上使用后缀。这意味着在数据库的逻辑、正常使用中，没有下划线；但在管理方面，下划线用作分隔符：

   _VView（TableName当然是main在前面）
   _fkForeign Key（约束名，不是列名）
   _cacCache
   _segSegment
   _trTransaction（存储过程或函数）
   _fnFunction（非事务性）等。

   格式是表或 FK 名称、下划线和操作名称、下划线，最后是后缀。

   这非常重要，因为当服务器向您提供错误消息时：

   ____blah blah blah error on object_name

   您确切地知道违反了什么对象，以及它试图做什么：

   ____blah blah blah error on Customer_Add_tr

9. 外键（约束，而不是列）。FK 的最佳命名是使用动词短语（减去“每个”和基数）。

   Customer_Initiates_SalesOrder_fk
Part_Comprises_Component_fk
Part_IsConsumedIn_Assembly_fk

   使用Parent_Child_fk序列，不是Child_Parent_fk因为 (a) 在您查找它们时它以正确的排序顺序显示，并且 (b) 我们总是知道所涉及的孩子，我们猜测的是哪个父母。然后错误消息令人愉快：

   ____ Foreign key violation on Vendor_Offers_PartVendor_fk.

   这对于那些费心为他们的数据建模的人很有效，其中动词短语已经被识别。对于其余的记录归档系统等，请使用Parent_Child_fk.

10. 索引是特殊的，因此它们有自己的命名约定，按顺序由 1 到 3 的每个字符位置组成：

    UUnique, or _for non-unique
    CClustered, or _for non-clustered
    _separator

    对于剩余部分：

    • 如果键是一列或几列：
      ____ColumnNames

    • 如果键多于几列：
      ____PK主键（根据型号）
      ____AK[*n*]备用键（IDEF1X 术语）

    注意，表名没有在指标名必需的，因为它总是显示为table_name.index_name.

    因此，当Customer.UC_CustomerId或Product.U__AK出现在错误消息中时，它会告诉您一些有意义的信息。当您查看表格上的索引时，您可以轻松区分它们。

11. 找一个合格和专业的人并跟随他们。查看他们的设计，并仔细研究他们使用的命名约定。向他们询问您不明白的任何具体问题。相反，如果任何人都对命名约定或标准漠不关心，那么就会像地狱一样奔跑。这里有一些让你开始：

    • 它们包含上述所有内容的真实示例。在此线程中提出重新命名问题的问题。
    • 当然，除了命名约定之外，这些模型还实现了其他几个标准；您可以暂时忽略这些，也可以随时提出具体的新问题。
    • 它们每个都有几个页面，Stack Overflow 的内联图像支持是针对鸟类的，并且它们在不同的浏览器上加载不一致；所以你必须点击链接。
    • 请注意，PDF 文件具有完整的导航功能，因此请单击蓝色玻璃按钮或标识扩展的对象：
    • 不熟悉关系建模标准的读者可能会发现IDEF1X 表示法很有帮助。

Order Entry & Inventorywith Standard-compliant Addresses

具有符合标准的地址的订单输入和库存

Simple inter-office Bulletinsystem for PHP/MyNonSQL

用于 PHP/MyNonSQL 的简单办公室间公告系统

Sensor Monitoringwith full Temporal capability

具有完整时间功能的传感器监控

Answers to Questions

问题解答

^{That cannot be reasonably answered in the comment space.}

^{在评论空间中无法合理回答。}

Larry Lustig:
... even the most trivial example shows ...
If a Customer has zero-to-many Products and a Product has one-to-many Components and a Component has one-to-many Suppliers and a Supplier sells zero-to-many Components and a SalesRep has one-to-many Customers what are the "natural" names the tables holding Customers, Products, Components, and Suppliers?

Larry Lustig：
......即使是最简单的例子也表明......
如果客户有零对多的产品，产品有一对多的组件，组件有一对多的供应商，而供应商的销售额为零多对多组件和销售代表有一对多客户 保存客户、产品、组件和供应商的表的“自然”名称是什么？

There are two major problems in your comment:

你的评论有两个主要问题：

1. You declare your example to be "the most trivial", however, it is anything but. With that sort of contradiction, I am uncertain if you are serious, if technically capable.

2. That "trivial" speculation has several gross Normalisation (DB Design) errors.

   • Until you correct those, they are unnatural and abnormal, and they do not make any sense. You might as well name them abnormal_1, abnormal_2, etc.

   • You have "suppliers" who do not supply anything; circular references (illegal, and unnecessary); customers buying products without any commercial instrument (such as Invoice or SalesOrder) as a basis for the purchase (or do customers "own" products?); unresolved many-to-many relationships; etc.

   • Once that is Normalised, and the required tables are identified, their names will become obvious. Naturally.

1. 你宣称你的例子是“最微不足道的”，但是，它绝不是。有这种矛盾，我不确定你是否认真，是否有技术能力。

2. 这种“微不足道”的推测有几个严重的标准化（DB 设计）错误。

   • 在你纠正这些之前，它们是不自然和不正常的，它们没有任何意义。您不妨将它们命名为异常_1、异常_2 等。

   • 你有不供应任何东西的“供应商”；循环引用（非法和不必要的）；客户在没有任何商业工具（例如发票或销售订单）作为购买基础的情况下购买产品（或客户“拥有”产品吗？）；未解决的多对多关系；等等。

   • 一旦标准化，并且确定了所需的表，它们的名称就会变得显而易见。自然。

In any case, I will try to service your query. Which means I will have to add some sense to it, not knowing what you meant, so please bear with me. The gross errors are too many to list, and given the spare specification, I am not confident I have corrected them all.

无论如何，我会尽力为您的查询提供服务。这意味着我将不得不添加一些意义，不知道你的意思，所以请耐心等待。严重的错误太多无法一一列举，而且鉴于备用规范，我不相信我已经全部纠正了它们。

• I will assume that if the product is made up of components, then the product is an assembly, and the components are used in more than one assembly.

• Further, since "Supplier sells zero-to-many Components", that they do notsell products or assemblies, they sell only components.

• 我会假设如果产品是由组件组成的，那么该产品就是一个组件，并且这些组件用于多个组件中。

• 此外，由于“供应商销售零对多组件”，他们不销售产品或组件，他们只销售组件。

Speculation vs Normalised Model

推测与归一化模型

In case you are not aware, the difference between square corners (Independent) and round corners (Dependent) is significant, please refer to the IDEF1X Notation link. Likewise the solid lines (Identifying) vs dashed lines (Non-identifying).

如果您不知道，方角（独立）和圆角（从属）之间的差异很大，请参阅 IDEF1X Notation 链接。同样，实线（识别）与虚线（非识别）。

... what are the "natural" names the tables holding Customers, Products, Components, and Suppliers?

...保存客户、产品、组件和供应商的表的“自然”名称是什么？

• Customer
• Product
• Component (Or, AssemblyComponent, for those who realise that one fact identifies the other)
• Supplier

• 顾客
• 产品
• Component（或者，AssemblyComponent，对于那些意识到一个事实识别另一个的人）
• 供应商

Now that I have resolved the tables, I don't understand your problem. Perhaps you can post a specificquestion.

现在我已经解决了表格，我不明白你的问题。也许你可以发布一个特定的问题。

VoteCoffee:
How are you handling the scenario Ronnis posted in his example where multiple relationships exist between 2 tables (user_likes_product, user_bought_product)? I may misunderstand, but this seems to result in duplicate table names using the convention you detailed.

VoteCoffee：
您如何处理 Ronnis 在他的示例中发布的场景，其中 2 个表（user_likes_product，user_bought_product）之间存在多个关系？我可能会误解，但这似乎会使用您详述的约定导致重复的表名。

Assuming there are no Normalisation errors, User likes Productis a predicate, not a table. Do not confuse them. Refer to my Answer, where it relates to Subjects, Verbs, and Predicates, and my response to Larry immediately above.

假设没有标准化错误，User likes Product则是谓词，而不是表。不要混淆它们。请参阅我的回答，它与主语、动词和谓词有关，以及我对上面的拉里的回答。

• Each table contains a setof Facts (each row is a Fact). Predicates (or propositions), are not Facts, they may or may not be true.

  • The Relational Modelis based on First Order Predicate Calculus (more commonly known as First Order Logic). A Predicate is a single-clause sentence in simple, precise English, that evaluates to true or false.

  • Further, each table represents, or is the implementation of, manyPredicates, not one.

• A query is a test of a Predicate (or a number of Predicates, chained together) that results in true (the Fact exists) or false (the Fact does not exist).

• Thus tables should be named, as detailed in my Answer (naming conventions), for the row, the Fact, and the Predicates should be documented (by all means, it is part of the database documentation), but as a separate list of Predicates.

• This is not a suggestion that they are not important. They are very important, but I won't write that up here.

• Quickly, then. Since the Relational Modelis founded on FOPC, the entire database can be said to be a set of FOPC declarations, a set of Predicates. But (a) there are many types of Predicates, and (b) a table does not represent one Predicate (it is the physical implementation of manyPredicates, and of different typesof Predicates).

• Therefore naming the table for "the" Predicate that it "represents" is an absurd concept.

• The "theoreticians" are aware of only a few Predicates, they do not understand that since the RMwas founded on the FOL, the entire database is a set of Predicates, and of different types.

  • And of course, they choose absurd ones from the few that they do know: EXISTING_PERSON; PERSON_IS_CALLED. If it were not so sad, it would be hilarious.

  • Note also that the Standard or atomic table name (naming the row) works brilliantly for all the verbiage (including all Predicates attached to the table). Conversely, the idiotic "table represents predicate" name cannot. Which is fine for the "theoreticians", who understand very little about Predicates, but retarded otherwise.

• The Predicates that are relevant to the data model, are expressed inthe model, they are of two orders.

  1. Unary Predicate
     The first set is diagrammatic, not text: the notation itself. These include various Existential; Constraint-oriented; and Descriptor (attributes) Predicates.

     • Of course, that means only those who can 'read' a Standard data model can read those Predicates. Which is why the "theoreticians", who are severely crippled by their text-only mindset, cannot read data models, why they stick to their pre-1984 text-only mindset.

  2. Binary Predicate
     The second set is those that form relationshipsbetween Facts. This is the relation line. The Verb Phrase (detailed above) identifies the Predicate, the proposition, that has been implemented (which can be tested via query). One cannot get more explicit than that.

     • Therefore, to one who is fluent in Standard data models, all the Predicates that are relevant, are documented in the model. They do not need a separate list of Predicates (but the users, who cannot 'read' everything from the data model, do!).

• Here is a Data Model, where I have listed the Predicates. I have chosen that example because it shows the Existential, etc, Predicates, as well as the Relationship ones, the only Predicates not listed are the Descriptors. Here, due to the seeker's learning level, I am treating him as an user.

• 每个表含有一组事实的（每一行是一个事实）。谓词（或命题）不是事实，它们可能是真的，也可能不是。

  • 的关系模型是基于一阶谓词演算（更通常被称为一阶逻辑）。谓词是简单、精确的英语中的单子句句子，其评估结果为真或假。

  • 此外，每个表都代表或实现了许多谓词，而不是一个。

• 查询是对结果为真（事实存在）或假（事实不存在）的谓词（或多个谓词，链接在一起）的测试。

• 因此，表应该被命名，如我的答案（命名约定）中详述的那样，对于行、事实和谓词应该被记录（无论如何，它是数据库文档的一部分），但作为一个单独的谓词列表.

• 这并不是暗示它们不重要。它们非常重要，但我不会在这里写。

• 那就快点。由于关系模型建立在FOPC之上，所以整个数据库可以说是一组FOPC声明，一组Predicates。但是 (a) Predicates 的类型很多，并且 (b) 一张表不代表一个 Predicates（它是许多Predicates 和不同类型Predicates的物理实现）。

• 因此，将表命名为它“代表”的“the”谓词是一个荒谬的概念。

• “理论家”只知道几个Predicates，他们不明白既然RM是建立在FOL之上的，整个数据库就是一组Predicates，而且是不同类型的。

  • 当然，他们选择从一些荒谬的人，他们知道：EXISTING_PERSON; PERSON_IS_CALLED. 如果不是那么悲伤，那就是搞笑了。

  • 另请注意，标准或原子表名称（命名行）适用于所有措辞（包括附加到表的所有谓词）。相反，愚蠢的“表代表谓词”名称不能。这对“理论家”来说很好，他们对谓词知之甚少，但在其他方面却迟钝。

• 是相关的数据模型的谓词，表示在该模型中，它们是两个订单。

  1. 一元谓词
     第一组是图解的，而不是文本：符号本身。这些包括各种存在；面向约束；和描述符（属性）谓词。

     • 当然，这意味着只有那些可以“读取”标准数据模型的人才可以读取这些谓词。这就是为什么被纯文本思维严重削弱的“理论家”无法阅读数据模型，为什么他们坚持 1984 年之前的纯文本思维模式。

  2. 二元谓词
     第二组是那些在事实之间形成关系的谓词。这是关系线。动词短语（上文详述）标识已实现的谓词，即命题（可以通过查询进行测试）。没有比这更明确的了。

     • 因此，对于精通标准数据模型的人来说，所有相关的谓词都记录在模型中。他们不需要单独的谓词列表（但是不能从数据模型“读取”所有内容的用户需要！）。

• 这是一个数据模型，我在其中列出了谓词。我选择这个例子是因为它显示了存在等谓词，以及关系，唯一没有列出的谓词是描述符。在这里，由于寻求者的学习水平，我将他视为用户。

Therefore the event of more than one child table between two parent tables is not a problem, just name them as the Existential Fact re their content, and normalise the names.

因此，两个父表之间的多个子表的事件不是问题，只需将它们命名为它们的内容的存在事实，并规范化名称。

The rules I gave for Verb Phrases for relationship names for Associative Tables come into play here. Here is a Predicate vs Tablediscussion, covering all points mentioned, in summary.

我为关联表的关系名称的动词短语给出的规则在这里发挥作用。这是谓词与表的讨论，概括地涵盖了提到的所有要点。

For a good short description re the proper use of Predicates and how to use them (which is quite a different context to that of responding to comments here), visit this answer, and scroll down to the Predicatesection.

有关正确使用谓词以及如何使用它们的简短描述（这与此处回复评论的上下文完全不同），请访问此答案，然后向下滚动到谓词部分。

---

---

Charles Burns:
By sequence, I meant the Oracle-style object purely used to store a number and its next according to some rule (e.g. "add 1"). Since Oracle lacks auto-ID tables, my typical use is to generate unique IDs for table PKs. INSERT INTO foo(id, somedata) VALUES (foo_s.nextval, "data"...)

Charles Burns：
按顺序，我的意思是 Oracle 风格的对象纯粹用于根据某些规则（例如“加 1”）存储一个数字及其下一个数字。由于 Oracle 缺少自动 ID 表，我的典型用途是为表 PK 生成唯一 ID。INSERT INTO foo(id, somedata) VALUES (foo_s.nextval, "data"...)

Ok, that is what we call a Key or NextKey table. Name it as such. If you have SubjectAreas, use COM_NextKey to indicate it is common across the database.

好的，这就是我们所说的 Key 或 NextKey 表。如此命名。如果您有 SubjectAreas，请使用 COM_NextKey 指示它在整个数据库中是通用的。

Btw, that is a very poor method of generating keys. Not scalable at all, but then with Oracle's performance, it is probably "just fine". Further, it indicates that your database is full of surrogates, not relational in those areas. Which means extremely poor performance and lack of integrity.

顺便说一句，这是一种非常糟糕的生成密钥的方法。根本没有可扩展性，但是以 Oracle 的性能来看，它可能“很好”。此外，它表明您的数据库充满了代理，在这些区域中没有关系。这意味着极差的性能和缺乏完整性。

回答by Ronnis

Singular vs. Plural: Pick one and stick with it.

单数与复数：选择一个并坚持下去。

Columns shouldn't be prefixed/suffixed/infixed or in anyway fixed with references to the fact that it is a column. The same goes for tables. Don't name tables EMPLOYEE_T or TBL_EMPLOYEES because the second it is replaced with a view, things get really confusing.

列不应该有前缀/后缀/中缀，或者以任何方式固定引用它是列的事实。桌子也是如此。不要将表命名为 EMPLOYEE_T 或 TBL_EMPLOYEES，因为当它被视图替换时，事情会变得非常混乱。

Don't embed type information in names, such as "vc_firstname" for varchar, or "flavour_enum". Also don't embed constraints in column names, such as "department_fk" or "employee_pk".

不要在名称中嵌入类型信息，例如 varchar 的“vc_firstname”或“flavour_enum”。也不要在列名称中嵌入约束，例如“department_fk”或“employee_pk”。

Actually, the only good thing about *fixes I can think of, is that you can use reserved words like where_t, tbl_order, user_vw. Of course, in those examples, using plural would have solved the issue :)

实际上，我能想到的关于 *fixes 的唯一好处是您可以使用诸如where_t, tbl_order, 之类的保留字user_vw。当然，在这些例子中，使用复数可以解决这个问题:)

Don't name all keys "ID". Keys refering to the same thing, should have the same name in all tables. The user id column could be called USER_ID in the user table and all tables referencing the user. The only time it is renamed is when different users are playing different roles, such as Message(sender_user_id, receiver_user_id). This really helps when dealing with larger queries.

不要将所有键都命名为“ID”。指向同一事物的键在所有表中应具有相同的名称。用户 id 列可以在用户表和所有引用用户的表中称为 USER_ID。只有在不同用户扮演不同角色时才会重命名，例如 Message(sender_user_id,receiver_user_id)。这在处理较大的查询时确实有帮助。

Regarding CaSe:

关于案例：

thisiswhatithinkofalllowercapscolumnnames.

ALLUPPERCAPSISNOTBETTERBECAUSEITFEELSLIKESOMEONEISSCREAMINGATME.

CamelCaseIsMarginallyBetterButItStillTakesTimeToParse.    

i_recommend_sticking_with_lower_case_and_underscore

In general it is better to name "mapping tables" to match the relation it describes rather than the names of the referenced tables. A user can have any number of relations to products: user_likes_product, user_bought_product, user_wants_to_buy_product.

一般来说，最好命名“映射表”以匹配它描述的关系而不是引用表的名称。用户可以与产品有任意数量的关系：user_likes_product、user_bought_product、user_wants_to_buy_product。

回答by Jonathan Leffler

There is no 'correct' about singular vs plural - it is mostly a matter of taste.

单数与复数没有“正确”之分——这主要是品味问题。

It depends in part on your focus. If you think of the table as a unit, it holds 'plurals' (because it holds many rows - so a plural name is appropriate). If you think of the table name as identifying a row in a table, you'll prefer 'singular'. This means your SQL will be thought of as working on one row from the table. That's OK, though it is usually an oversimplification; SQL works on sets (more or less). However, we can go with singular for the answers to this question.

这部分取决于你的注意力。如果您将表格视为一个单位，则它包含“复数”（因为它包含许多行 - 因此复数名称是合适的）。如果您将表名视为标识表中的一行，您会更喜欢“单数”。这意味着您的 SQL 将被视为处理表中的一行。没关系，尽管它通常过于简单化；SQL 适用于集合（或多或少）。但是，我们可以用单数来回答这个问题。

1. Since you'll probably need a table 'user', another 'product', and the third to connect users to products, then you need a table 'user_product'.

2. Since the description applies to a product, you would use 'product_description'. Unless each user names each product for themselves...

3. The 'user_product' table is (or could be) an example of a table with a product ID and a user ID and not much else. You name the two-attribute tables in the same general way: 'user_stuff'. Decorative prefixes like 'rel_' don't really help. You'll see some people using 't_' in front of each table name, for instance. That is not a lot of help.

1. 由于您可能需要一个表 'user'、另一个 'product' 和第三个将用户连接到产品，那么您需要一个表 'user_product'。

2. 由于描述适用于产品，您将使用“product_description”。除非每个用户都为自己命名每个产品......

3. 'user_product' 表是（或可能是）具有产品 ID 和用户 ID 的表的示例，除此之外别无他物。您以相同的一般方式命名两个属性表：'user_stuff'。像“rel_”这样的装饰性前缀并没有真正的帮助。例如，您会看到有些人在每个表名前使用“t_”。这不是很多帮助。

回答by amelvin

Plurals aren't bad as long as they are used consistently - but singular is my preference.

只要一直使用复数就不错了——但我更喜欢单数。

I would dispense with underscores unless you want to outline a many-to-many relationship; and use an initial capital because it helps distinguish things in ORMs.

除非您想概述多对多关系，否则我会省略下划线；并使用初始资本，因为它有助于区分 ORM 中的事物。

But there are many naming conventions, so if you want to use underscores that's OK as long as its done consistently.

但是有很多命名约定，所以如果你想使用下划线，只要它一致地完成就可以了。

So:

所以：

User

UserProduct (it is a users products after all)

If only one user can have any product then

如果只有一个用户可以拥有任何产品，那么

UserProductDescription

But if the product is shared by users:

但如果产品被用户共享：

ProductDescription

If you save your underscores for many-to-many relationships you can do something like:

如果您为多对多关系保存下划线，您可以执行以下操作：

UserProduct_Stuff

to form a M-to-M between UserProduct and Stuff - not sure from the question the exact nature of the many-to-many required.

在 UserProduct 和 Stuff 之间形成 M-to-M - 从问题中不确定所需的多对多的确切性质。

回答by Ozzy

There is not more correct to use singular than plural form, where have you heard that? I would rather say that plural form is more common for naming database tables...and in my opinion also more logic. The table most often contain more than one row ;) In a conceptual model though the names of the entities are often in singular.

使用单数比使用复数更正确，你在哪里听说过？我宁愿说复数形式在命名数据库表时更常见……而且在我看来也更具逻辑性。该表通常包含不止一行；) 在概念模型中，尽管实体的名称通常是单数。

About your question, if 'Product' and 'ProductDescription' are concepts with an identity (i.e. entities) in your model I would simply call the tables 'Products' and 'ProductDescriptions'. For tables that are used in order to implement a many-to-many relationship I most often use the naming convention "SideA2SideB", for example "Student2Course".

关于您的问题，如果“产品”和“产品描述”是模型中具有身份（即实体）的概念，我将简单地将表称为“产品”和“产品描述”。对于用于实现多对多关系的表，我最常使用命名约定“SideA2SideB”，例如“Student2Course”。