关系表命名约定 [英] Relational table naming convention

问题描述

我开始一个新项目，想从一开始就得到我的表格和列名。例如，我一直在表名中使用复数，但最近学习的奇异是正确的。

所以，如果我有一个表用户，然后我得到的产品用户将有，
应该表名为user_product还是只是产品？这是一对多的关系。

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

如果if想要一个纯关系表（多对多）只有两列，这会是什么样子？ user_stuff或者像rel_user_stuff？如果是第一个，什么会区分这与例如user_product？

任何帮助是高度赞赏，如果有某种命名约定标准

感谢

解决方案

表格•名称

最近学习的单数是正确的 b $ b

是的。当心耶和华。表名中的多个 是没有阅读任何标准材料并且不具有数据库理论知识的人的确切符号。

有关标准的一些奇妙的事情是：它们都彼此集成;他们一起工作;他们是由大于我们的心灵写的，所以我们不必辩论他们。标准表格名称是指表格中的每个行，用于所有语言，而不是表格的总内容（我们知道 Customer 表包含所有客户）。

关系，动词短语

在已建模的真正的关系数据库中（与在SQL数据库容器中实现的记录归档系统相反）：

• 主题，因此它们是名词，再次是单数


• 表之间的关系是 / em>，因此它们是 （即它们不是任意编号或命名）


• > 谓词


• 所有可以直接从数据模型中读取的内容（请参阅我的末尾示例）


• （独立表的谓词（层次结构中最顶层的父）是独立的）


• ，因此仔细选择动词短语它是最有意义的，通用的术语被避免（这变得更容易与经验）。动词短语在建模期间很重要，因为它有助于解决模型，即。澄清关系，识别错误和更正表名称。

Diagram_A

当然，子表中的外键约束（more，later）。这里是动词短语（在模型中），它代表的谓词（从模型中读取）和FK 约束名称：

 启动
每个客户启动0到n个SalesOrders 
 Customer_Initiates_SalesOrder_fk 
  
 
 
 
 表格语言 
 
 
 
 ，在描述表时，特别是在诸如谓词或其他文档的技术语言中，使用单数和复数，因为它们在英语中是自然的。请记住表以单行（关系）命名，语言是指每个派生行（派生关系）：
 每个客户启动零到多销售订单
  
不是
 客户有零个到多个销售订单
  
 所以，如果我得到一个表用户，然后我得到只有用户将有的产品，应该表名为用户产品还是只是产品？这是一对多的关系。 
 
 
（这不是一个命名约定问题;问题。）如果 user :: product 是1 :: n无关紧要。重要的是产品是否是一个单独的实体，以及是否为独立表，即。它可以自己存在。因此，产品，而不是 user_product 。 
 
 
 
如果产品仅存在于用户。它是一个依赖表，因此 user_product 。
 
 
 
   Diagram_B   
 
 
 更进一步，如果我将（为某种原因）每个产品的几个产品描述，将是用户产品描述或产品描述或只是描述 ？当然，用正确的外键设置..命名它只有描述会有问题，因为我也可以有用户描述或帐户描述或任何。 
 
 
没错。根据上述内容， user_product_description  xor  product_description 它不是区别于其他 xxxx_descriptions ，但它是给名称感觉它在哪里，前缀是父表。
 
 
如果我想要一个只有两列的纯关系表（多对多），该怎么样？ user-stuff或者像rel-user-stuff这样的东西？如果是第一个，那么会区分什么，例如用户产品？ 
 
 
 
 
 
 
 
 
希望关系数据库中的所有表都是纯关系规范化表。没有必要在名称中标识（否则所有表将 rel_something ）。
 
 
 
 
如果它包含仅两个父节点的PK（它们将不作为逻辑层上的实体存在的逻辑 n :: n关系） em>物理表），即关联表。是的，通常名称是两个父表名的组合。 
 
 
 
 
 
 
请注意，这是Verb Phrase适用的情况，并且读为，从父项到父项，忽略子表，因为它在生活中的唯一目的是关联两个父母。
 
 
 
   Diagram_C   
 
 
 
 
如果不是表（即，除了两个PK，它包含数据），然后适当地命名，并且动词短语适用于它，而不是父关系的结束。 
 
 
 
   Diagram_D   
 
 
 
 
 
 
 
如果您最终得到两个 user_product 表，那么这是一个非常大的信号，你没有规范化的数据。所以回去几个步骤，并做到这一点，并准确和一致地命名表。 
 
 
 
 
 
 
命名约定
 
 
 
 
 
 任何帮助都非常感谢，如果你有一些你建议的命名约定标准，请随时链接。 
 
 
您正在做的是非常重要的，它会影响每个级别的易用性和理解。因此，一开始尽可能多地了解是很好的。 
 
 
 
 案例是要解决的第一个项目。所有上限是不可接受的。混合大小写是正常的，特别是如果表可以由用户直接访问。参考我的数据模型。注意，当探索者使用一些痴呆的NonSQL，只有小写，我给出，在这种情况下，我包括下划线（根据你的例子）。 
 
 
 
 
保持数据焦点，而不是应用程序或使用重点。它是，自2011年以来，我们已经有开放架构自1984年以来，数据库应该是独立的使用它们的应用程序。 
 
 
 
这样，随着应用程序的增长，并且超过一个应用程序使用它们，命名将保持有意义，不需要更正。 （完全嵌入单个应用程序的数据库不是数据库。）仅将数据元素命名为数据。
 
 
 
 
列准确。如果是 DATETIME 数据类型，请不要使用 UpdatedDate ，请使用 UpdatedDtm 。 
 
 
 
 
重要的是一致性。不要在一个地方使用 NumProduct 表示产品数量， ItemNo 或 ItemNum 在另一个地方指示项目数。使用 NumSomething 用于数字的和 SomethingNo 或 SomethingId  
 
 
 
 
不要在列名称前添加表名或短代码，例如 user_first_name 。 SQL已经提供了tablename作为限定符：
  table_name.column_name  - 注意点
  
 
 
 
 
例外：
 
 
 
 
 
 
第一个例外是PKs，他们需要特殊的处理，因为你一直在连接中对它们进行编码，并且希望键从数据列中脱颖而出。始终使用 user_id ，从不 id 。
 
 
 
 
 
请注意，这不是用作前缀的表名，而是键的组件的正确描述性名称： user_id 是标识用户的列，而不是用户表的 id 。
 
 
 
 
（除了在记录文件系统中，其中文件由代理访问，并且没有关系键，它们是一个相同的东西）。
 
 
 
 
 
对于携带（迁移）PK的密钥列，始终使用完全相同的名称作为FK。
 
 
  user_product 表将有一个 user_id 作为其PK （user_id，product_no） 。
 
 
当您开始编码时，这一点的相关性将变得清晰。首先，在许多表上使用 id ，很容易在SQL编码中混淆。第二，任何其他人，最初的编码器不知道他在做什么。 
 
 
 
 
 
 
第二个例外是多个FK引用相同的父表表，在孩子中携带。根据关系模型，使用角色名称来区分意义或用法，例如。  AssemblyCode 和 ComponentCode 两个 PartCodes 。在这种情况下，不对其中一个使用未区分的 PartCode 。精确。 
 
 
 
   Diagram_E   
 
 
 
 
 
 
 
 前缀 
 
如果您有超过100个表，请在表名前加上主题区：
 
 
 
  REF _  
 
  OE _ 用于订单输入集群等。
 
 
 
 
 
 
 
 
 后缀 
 
不要在表格上使用后缀，总是在其他一切使用后缀。这意味着在逻辑上，正常使用数据库，没有下划线;但在管理方面，下划线用作分隔符：
 
 
 
  _V  $ c> TableName 当然）
 
  _fk 外键（约束名称，而不是列名称） 
 
  _cac 缓存
 
  _seg 段
 
  _tr 事务（存储过程或函数）
 
  _fn 函数等等。
 
 
 
格式是表或FK名称，下划线和操作名称，下划线，最后是后缀。 
 
 
 
这非常重要，因为当服务器给您一个错误消息：
 
 
 
 ____  blah blah blah错误对object_name  
 
 
 
你确切知道是什么对象被违反，它试图做什么：
 
 
 
 ____  Customer_Add_tr上的错误blah blah blah  
 
 
 
 
 外键（约束，而不是列）。 FK的最佳命名是使用动词短语（减去每个和基数）。
 
 
 
  Customer_Initiates_SalesOrder_fk  
 
  Part_Comprises_Component_fk  
 
  Part_IsConsumedIn_Assembly_fk  
 
 
 
使用 Parent_Child_fk 序列，而不是 Child_Parent_fk 是因为在正确的排序顺序，当你在寻找他们和（b）我们总是知道涉及的孩子，我们正在猜测是，哪个父母。错误讯息很愉快：
 
 
 
 ____  Vendor_Offers_PartVendor_fk 上的外键违规。
 
 
 
这对于那些打算模拟数据的人来说非常有用，因为这里已经确定了动词短语。对于其余的，记录归档系统等，使用 Parent_Child_fk 。
 
 
 
 
他们有自己的命名约定，由按顺序，每个字符位置从1到3：
 
 
 
  U  Unique或 _ 用于非唯一
 
  C  Clustered或 _ 用于非群集
 
  _  separator 
 
 
 
剩余部分：
 
 
 
 
 
 
如果键是一列或非常少列：
 
 ____  ColumnNames  
 
 
 
 
如果键超过几个列：
 
 ____  PK 主键（按型号）
 
 ____  AK [* n * ] 替代键（IDEF1X字词）
 
 
 
 
 
 
请注意，  c> 
 
 
 
因此，在索引名称中不需要当错误消息中出现 Customer.UC_CustomerId 或 Product.U__AK 时，它会告诉您有意义的事。 
 
 
 
 
找一个合格的专业人士，跟随他们。看看他们的设计，并研究他们使用的命名约定。向他们询问有关你不了解的任何事情的具体问题。相反，运行像地狱从任何人谁表现出很少考虑命名约定或标准。这里有几个让你开始：
 
 
 
 
 
它们包含所有上述的实例。 
 
 
当然，除了命名约定，模型还实现了几种其他标准;您现在可以忽略这些内容，也可以随意提出具体的新问题。
 
 
它们分别是多个页面，SO的内联图像支持，并且它们不会在不同的浏览器上一致加载; 
 
 
请注意，PDF文件具有完全导航，因此请点击蓝色玻璃按钮或识别出扩展的对象：
 
 
不熟悉关系建模标准的读者可能会发现  IDEF1X记法 。
 
 
 
 
 
  订单输入&库存 符合标准的地址
 
 
 
简单的办公室文档/学生％20分辨率/ Andrew / Andrew％202％20DM.pdf> 公告 系统
 
 
 
 a href =http://www.softwaregems.com.au/Documents/Student%20Resolutions/Mark%20DM.pdf> 传感器监控 。
 
 
 
对问题的回答
 
 
 
 在评论空间中无法合理回答。 
 
 
 
 
  Larry Lustig：
 
 ...即使最微不足道的例子也显示... 
 
如果客户具有零对多的产品，并且产品具有一对多组件，并且组件具有一对多供应商，并且供应商销售零对多组件，并且SalesRep具有一对多客户 
 
 
有两个主要的您的评论中出现问题：
 
 
 
您声明您的示例是最微不足道的，但它不是。 
 
 
 
 
这种琐碎的猜测有几个大规模化（DB设计）错误。 
 
 
 
 
 
 
直到纠正它们，它们是不自然的和异常的，他们没有任何意义。 
 
 
 
 
你有供应商谁不提供任何东西;循环引用（非法和不必要）;客户购买产品没有任何商业仪器（如发票或销售订单）作为购买的基础（或做客户自己的产品）？未解决的多对多关系; 
 
 
 
 
一旦标准化，并且标识了所需的表，它们的名称就会变得明显。自然。
 
 
 
 
 
 
无论如何，您的查询。这意味着我必须增加一些感觉，不知道你的意思，所以请请与我一起。 
 
 
 
 
 
 
 
我假设如果产品是由组件组成的，那么产品是一个组件，并且这些组件在多个组件中使用。 
 
 
 
 
此外，由于供应商销售零件到多个组件，他们不是销售产品或组件， 
 
 
 
 
 
 
  猜测与归一化模型  
 
 
 
如果您不知道，方角（独立）和圆角（依赖）是重要的，请参考IDEF1X Notation链接。同样，实线（识别）vs虚线（非识别）。
 
 
  
 
 
 
 
 
 
 
客户 li> 
 
产品
 
 
组件
（或者，AssemblyComponent，用于意识到一个事实标识其他事件的人）
 
 
供应商
 
 
 
 
 
现在我已解决表格，我不明白您的问题。您可以发布具体问题。
 
 
  VoteCoffee：
 
你是否在处理Ronnis在他的示例中发布的场景，其中有两个表（user_likes_product，user_bought_product）之间存在多个关系？我可能会误解，但这似乎会导致使用您详细约定的重复表名称。 
 
 
规范化错误，用户喜欢产品是一个谓词，而不是表。不要混淆他们。 
 
 
 
 
 
 
每个人都可以参加我的回答，其中涉及主题，动词和谓词表包含一组Facts（每一行都是一个Fact），而不是谓词。谓词（或命题），不是事实，他们可能是也可能不是真的。 
 
 
 
 
 
 关系模型是基于一阶谓词演算法（一般称为一阶逻辑）。谓语是简单，精确的英语中的单语句，评价为真或假。 
 
 
 
 
 
 
查询是对谓语（或多个谓词，链接在一起） 
 
 
 
 
因此，在我的答案（命名约定）中，表格应该命名为，事实和谓词应该被记录（通过一切手段，它是数据库文档的一部分），但作为单独的谓语列表。
 
 
 
 
这不是一个建议，他们不重要。他们非常重要，但我不会在这里写。 
 
 
 
 
快速，然后。由于关系模型建立在FOL之上，整个数据库可以说是一组声明，一组谓词。但是（a）有许多类型的谓词，（b）表不表示谓语（它是许多谓词的物理实现，而且是不同的类型的谓词）。 
 
 
 
 
因此，将表格命名为它代表的谓词是一个荒谬的概念。 
 
 
 
 
理论家只知道几个谓语，他们不明白，因为 RM  ，整个数据库是一组谓词，并且是不同类型的。 
 
 
 
 
 
 
当然，他们选择几个荒谬的人，他们知道：EXISTING_PERSON; PERSON_IS_CALLED。 
 
 
 
 
请注意，标准或原子表名称（命名行）对所有语句都有着出色的表现（包括附加到表中的所有谓语）。相反，愚蠢的表表示谓词名称不能。 
 
 
 
 
 
 
 
理论家与数据模型相关的谓词在 模型中表示，它们有两种类型。 
 
 
 
 
 
 
第一组是图表，而不是文本，形式：符号。这些包括各种存在;约束导向;和描述符（属性）谓词。
 
 
 
 
当然，这意味着只有那些能够读取标准数据模型的人才能读取这些谓词。这就是为什么理论家，他们的文本唯一的心态严重削弱，不能读取数据模型，为什么他们坚持他们1984年之前的纯文字心态。
 
 
 
 
第二组是形成Facts之间关系的谓词。这是关系线。动词短语（以上详述）标识已经实现的谓词（命题）（其可以通过查询测试）。 
 
 
 
 
因此，对于能够流利使用标准数据模型的人来说，所有与谓词相关的谓词 >，在模型中记录。 
 
 
 
 
 
 
 
这里是一个 数据模型 ，其中I已列出谓语。我选择了这个例子，因为它显示了Existential等等，谓词，以及关系，只有没有列出的谓词是描述符。 
 
 
 
 
 
 
因此，事件超过了两个父表之间的一个子表不是问题，只是将它们命名为Existential Fact，并将其名称标准化。
 
 
 
我为动词短语给出的规则关联表的关系名称在这里起作用。以下是 谓词与表格 的讨论，涵盖所有点
 
 
 
有关良好的简短描述，如何正确使用谓词以及如何使用它们（这是与在此处回复评论的上下文不同的上下文） ），请访问 此答案 ，向下滚动到谓词 
 
 
 
 
 
 
 
 
 Charles Burns：
 
按顺序，我的意思是Oracle风格的对象纯粹用于存储一个数字及其下一个根据一些规则（例如add 1）。由于Oracle缺少自动ID表，我的典型用途是为表PK生成唯一的ID。 INSERT INTO foo（id，somedata）VALUES（foo_s.nextval，data...）
 
 
这就是我们所说的Key或NextKey表。将其命名为。如果你有SubjectAreas，使用COM_NextKey来表示它是数据库中常见的。
 
 
 
 Btw，这是一个很差的生成密钥的方法。不可扩展，但是随着Oracle的性能，它可能是只是罚款。此外，它表明您的数据库充满了代理，而不是这些领域的关系。这意味着极差的表现和缺乏诚信。
 
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.

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

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"?

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
解决方案
Table • Name

  
recently learned singular is correct  
Yes.  Beware of the heathens.  Plural in the table names are 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; and they were written by minds greater than ours, so we do not have to debate them.  The standard table name refers to each row in the table, which is used in the all verbiage, not the total content of the table (we know that the Customer table contains all the Customers).

Relationship, Verb Phrase

In genuine Relational Databases that have been modelled (as opposed to record filing systems implemented in an SQL database container):


the tables are the Subjects of the database, thus they are nouns, again, singular
the relationships between the tables are the Actions that take place between the nouns, thus they are verbs (i.e they are not arbitrarily numbered or named)
that is the 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 Phrase is 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

Of course, the relationship is implemented in SQL as a Foreign Key Constraint in the child table (more, later).  Here is the Verb Phrase (in the model), the Predicate that it represents (to be read from the model), and the FK Constraint Name:
    Initiates
    Each Customer Initiates 0-to-n SalesOrders
    Customer_Initiates_SalesOrder_fk
Table • Language

However, when describing the 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.
(That is not a naming-convention question; that is a a db design question.) It doesn't matter if user::product is 1::n.  What matters is whether product is a separate entity and whether it is an Independent Table, ie. it can exist on its own.  Therefore product, not user_product.  

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

Diagram_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_description xor product_description will 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.

  
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"?



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).
If it contains only the PKs of the two parents (which resolves the logical n::n relationship that does not exist as an entity at the logical level, into a physical table), 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 not an 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

If you end up with two user_product tables, 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.


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.

Case is 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). 
Maintain a data focus, not an application or usage focus.  It is, after all 2011, we have had Open Architecture since 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.
Be very considerate, and name tables and columns very accurately.  Do not use UpdatedDate if it is a DATETIME datatype, use UpdatedDtm.  Do not use_description if it contains a dosage.
It is important to be consistent across the database. Do not use NumProduct in one place to indicate number of Products and ItemNo or ItemNumin another place to indicate number of Items.  Use NumSomething for numbers-of, and SomethingNo or SomethingId for identifiers, consistently.
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

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 not a table name used as a prefix, but a proper descriptive name for the component of the key: user_id is the column that identifies an user, not the id of the user table.


(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_product table will have an user_id as a component of its PK (user_id, product_no).
the relevance of this will become clear when you start coding.  First, with an id on 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 Names to differentiate the meaning or usage, eg. AssemblyCode and ComponentCode for two PartCodes.  And in that case, do not use the undifferentiated PartCode for one of them.  Be precise. 

Diagram_E

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

_V View (with the main TableName in front, of course)

_fk Foreign Key (the constraint name, not the column name)

_cac Cache

_seg Segment

_tr Transaction (stored proc or function)

_fn Function (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  
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_fk sequence, not Child_Parent_fk is 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.
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:  

U Unique, or _ for non-unique

C Clustered, 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:

____PK Primary Key (as per model)

____AK[*n*] Alternate Key (IDEF1X term)  


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

So when Customer.UC_CustomerId or Product.U__AK appears in an error message, it tells you something meaningful.  When you look at the indices on a table, you can differentiate them easily.
Find someone qualified and professional and follow them.  Look at their designs, and 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 other Standards, 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 SO 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 Notation helpful.

Order Entry & Inventory with Standard-compliant Addresses

Simple inter-office Bulletin system for PHP/MyNonSQL

Sensor Monitoring with 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?
There are two major problems in your comment:

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

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

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


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

  
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.
Assuming there are no Normalisation errors, User likes Product is 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.


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


The Relational Model is 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.   

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 Model is founded on FOL, the entire database can be said to be a set of declarations, a set of Predicates.  But (a) there are many types of Predicates, and (b) a table does not represent a Predicate (it is the physical implementation of many Predicates, and of different types of 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 RM was 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 in the model, they are of two kinds. 


The first set is in diagrammatic, not text, form: the notation.  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.
The second set is those Predicates that form relationships between Facts.  This is the relationship 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 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.


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 Table discussion, 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 Predicate section.




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

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.

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