Table • Name个

recently learned singular is correct

是.当心异教徒.数字in the table names是一个明确的标志，表明某人没有读过任何标准material ，也没有数据库理论的知识.

关于标准的一些美妙之处是:

• 它们都是相互融合的
• 他们一起工作
• they were written by minds greater than ours, so we do 不 have to debate them.

标准表名指表中的每个row，它在ALL术语中使用，而不是表的全部内容(我们知道客户表包含所有客户).

Relationship, Verb Phrase个

在已经建模的真正的关系数据库中(与1970年以前的记录归档系统不同[其特点是在SQL数据库容器中实现Record IDs个，以方便使用]:

• 这些表是数据库的前Subjects个，因此它们也是nouns个单数
• the relationships between the tables are the Actions that take place between the nouns, thus they are verbs (i.e they are 不 arbitrarily numbered or named)
• 那是is比谓语
• 可以直接从数据模型读取的所有内容(请参阅末尾的示例)
• (独立表(层次 struct 中最顶层的父表)的谓词是独立的)
• 因此，Verb Phrase是经过精心 Select 的，所以它是最有意义的，并且避免了通用术语(这随着经验的增加变得更容易).动词短语在建模过程中很重要，因为它有助于解析模型，即.澄清关系、识别错误并更正表名.

当然，该关系在SQL中实现为子表中的CONSTRAINT FOREIGN KEY(稍后会有更多信息).下面是Verb Phrase(在模型中)、它表示的谓语(要从模型中读取)和FK Constraint Name:

    Initiates
    Each 客户 Initiates 0-to-n SalesOrders
    客户_Initiates_SalesOrder_fk

Table • Language

然而，when describing表格，特别是在技术语言(如谓词)或其他文档中，使用单数和复数，因为它们在英语中很自然.请记住，该表是按单行(关系)命名的，而语言指的是每个派生行(派生关系):

    Each 客户 initiates zero-to-many SalesOrders

不

    客户s 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 不 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, 不 user_product.

并且如果product仅存在于user的上下文中，即，它是Dependent Table，因此是user_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.

That's right. Either user_product_description xor product_description will be correct, based on the above. It is 不 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"?个

1. 希望关系数据库中的所有表都是纯关系、规范化的表.不需要在名称中标识这一点(否则所有表都将是rel_something).

2. If it contains only the PKs of the two parents (which resolves the logical n::n relationship that does 不 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.

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

     [**Diagram_C**][Diagram_C]

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

     [**Diagram_D**][Diagram_D]

3. If you end up with two user_product tables, then that is a very loud signal that you have 不 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 不 be clear, until you start coding in SQL.

1. Case是第一个要解决的问题.所有的上限都是不可接受的.混合情况是正常的，尤其是当用户可以直接访问这些表时.参考我的数据模型.请注意，当搜索者使用一些只有小写字母的疯狂非SQL时，我会给出，在这种情况下，我会使用下划线(根据您的示例).

2. Maintain a data focus, 不 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 不 databases.) Name the data elements as data, only.

3. Be very considerate, and name tables and columns very accurately. Do 不 use UpdatedDate if it is a DATETIME datatype, use UpdatedDtm. Do 不 use_description if it contains a dosage.

4. It is important to be consistent across the database. Do 不 use Num产品 in one place to indicate number of 产品s and ItemNo or ItemNumin a不her place to indicate number of Items. Use NumSomething for numbers-of, and SomethingNo or SomethingId for identifiers, consistently.

5. Do 不 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  -- 不ice the dot
   

6. 例外情况:

   • 第一个例外是对于PKs，它们需要特殊处理，因为您总是在联接中对它们进行编码，并且您希望键从数据列中脱颖而出.一定要用user_id，千万不要用id.

     • Note that this is 不 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, 不 the id of the user table.
       • (当然，除了在记录归档系统中，文件是由代理访问的，没有关系键，它们是一回事).
     • 无论主键作为FK承载(迁移)到哪里，键列始终使用完全相同的名称.
     • 因此，user_product表将具有user_id作为其PK(user_id, product_no)的组件.
     • 当您开始编码时，这一点的相关性将变得清晰.首先，由于许多表上都有id，所以很容易在SQL编码中混淆.其次，除了最初的编码者之外，其他任何人都不知道他想要做什么.如果按上述方式处理键列，则这两种情况都很容易防止.

   • 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 不 use the undifferentiated PartCode for 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, 不 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:

   _V View (with the main TableName in front, of course)
   _fk Foreign Key (the constraint name, 不 the column name)
   _cac Cache
   _seg Segment
   _tr Transaction (stored proc or function)
   _fn Function (non-transactional), etc.

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

这一点非常重要，因为当服务器给您一条错误消息时:

_blah blah blah error on object_name

您确切地知道被侵犯的是什么对象，以及它试图做什么:

____blah blah blah error on 客户_Add_tr

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

   客户_Initiates_SalesOrder_fk
Part_Comprises_Component_fk
Part_IsConsumedIn_Assembly_fk

Use the Parent_Child_fk sequence, 不 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.

这对于那些费心为数据建模的人来说非常有效，因为动词短语已经被识别.其余的记录归档系统等使用Parent_Child_fk.

9. 索引很特殊，因此它们有自己的命名约定，由in order个字符组成，每个字符位置从1到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 不 required in the index name, because it always shows up as table_name.index_name.

因此，当客户.UC_客户Id或产品.U__AK出现在错误消息中时，它会告诉您一些有意义的信息.当你查看表格上的索引时，你可以很容易地区分它们.

99. 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 不 understand. Conversely, run like hell from anyone who demonstrates little regard for naming conventions or standards. Here's a few to get you started:

• 它们包含了上述所有的真实例子.问问题在此帖子中重新命名问题.
• 当然，这些模型实现了几个other标准，超出了命名约定；您可以暂时忽略这些标准，也可以随意询问特定的new questions标准.
• They are several pages each, inline image support at Stack Overflow is for the birds, and they do 不 load consistently on different browsers; so you will have to click the links.
• 请注意，PDF文件具有全导航功能，因此请单击蓝色玻璃按钮或标识展开的对象:
• 不熟悉关系建模标准的读者可能会发现这IDEF1X Notation个标准很有帮助.

Order Entry & Inventory个符合标准的地址

用于PHP/MyNonSQL的简单inter office Bulletin系统

Sensor Monitoring，具有完整的临时功能

Answers to Questions

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

Larry Lustig:
... even the most trivial example shows ...
If a 客户 has zero-to-many 产品s and a 产品 has one-to-many Components and a Component has one-to-many 供应商s and a 供应商 sells zero-to-many Components and a SalesRep has one-to-many 客户s what are the "natural" names the tables holding 客户s, 产品s, Components, and 供应商s?

你的 comments 中有两个主要问题:

1. 您宣称您的示例是"最微不足道的"，然而，它绝非如此.有了这样的矛盾，我不知道你是不是认真的，是不是有技术能力.

2. 这种"微不足道"的推测有几个严重的规范化(DB设计)错误.

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

• You have "suppliers" who do 不 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.

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

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

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

• Further, since "供应商 sells zero-to-many Components", that they do 不 sell products or assemblies, they sell only components.

Speculation vs Normalised Model个

In case you are 不 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 客户s, 产品s, Components, and 供应商s?个

• 客户
• 产品
• 组件 (或者，对于那些意识到一个事实标识另一个事实的人来说，是AssemblyComponent)
• 供应商

既然我已经解决了表格，我不明白你的问题.也许你可以发布specific个问题.

谓语

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 产品 is a predicate, 不 a table. Do 不 confuse them. Refer to my Answer, where it relates to Subjects, Verbs, and 谓语s, and my response to Larry immediately above.

• Each table contains a set of Facts (each row is a Fact). 谓语s (or propositions), are 不 Facts, they may or may 不 be true.

• Relational Model基于一阶谓词演算(通常称为一阶逻辑).谓语是简单、精确的英语中的单句句子，计算结果为真或假.

• Further, each table represents, or is the implementation of, many 谓语s, 不 one.

• A query is a test of a 谓语 (or a number of 谓语s, chained together) that results in true (the Fact exists) or false (the Fact does 不 exist).

• 因此，表应该按照我的回答(命名约定)中的详细说明进行命名，因为行、事实和谓词应该被记录(当然，它是数据库文档的一部分)，但作为谓词的单独列表.

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

• Quickly, then. Since the Relational Model is founded on FOPC, the entire database can be said to be a set of FOPC declarations, a set of 谓语s. But (a) there are many types of 谓语s, and (b) a table does 不 represent one 谓语 (it is the physical implementation of many 谓语s, and of different types of 谓语s).

• 因此，将表命名为它"代表"的谓词是一个荒谬的概念.

• The "theoreticians" are aware of only a few 谓语s, they do 不 understand that since the RM was founded on the FOL, the entire database is a set of 谓语s, 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 不 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 谓语s attached to the table). Conversely, the idiotic "table represents predicate" name can不. Which is fine for the "theoreticians", who understand very little about 谓语s, but retarded otherwise.

• 与数据模型相关的谓词在模型中表示in，它们是两阶的.

1. Unary 谓语
   The first set is diagrammatic, 不 text: the 不ation itself. These include various Existential; Constraint-oriented; and Descriptor (attributes) 谓语s.

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

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

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

• Here is a Data Model, where I have listed the 谓语s. I have chosen that example because it shows the Existential, etc, 谓语s, as well as the Relationship ones, the only 谓语s 不 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 不 a problem, just name them as the Existential Fact re their content, and normalise the names.

我为关联表的关系名称提供的动词短语规则在这里起作用.下面是谓语 vs Table次讨论，总结一下，涵盖了所有提到的问题.

关于谓词的正确使用以及如何使用谓词的简短描述(这与这里回复 comments 的上下文完全不同)，请访问this answer，并向下滚动到谓语部分.

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

好的，这就是我们所说的键或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, 不 relational in those areas. Which means extremely poor performance and lack of integrity.