数据库规范化设计 - 单表或多表 [英] Database normalization design - single or multiple tables

问题描述

这个数据库应该用1表或3表表示？我和我的朋友对此有不同的看法，所以我想看到一般的看法。 （也许它应该是一个投票的任何解决方案？）

 创建表顺序
 //表的基本字段
  -  ID（主键）
  -  CustomerID（带FK的整数）
  - 数量
  -  ProductID（带FK的整数）
 
 //然后根据用户选择，需要指定这些字段
 //（可以被分解为单独的表）：
 {
  -  InternalAccountID（带有FK的整数） 
  -  InternalCompanyID（整数，带有FK）
} 
 
 //或者这些（可以分解为单独的表）：
 {
 -  ExternalAccountNumber（自由文本字符串）
  -  ExternalCompanyName（自由文本字符串）
  -  ExtraInformation（自由文本字符串）
} 
  

1表格方式：

优点：

• 性能（一个插入而不是两个，FK检查，没有连接）


• 可能需要更少的空间（额外的表有开销+索引+额外的ID字段） / li>
  
• 一个表相对于三个


• 几乎没有理由将其分配到新的表格，仅用于2 + 3字段（或什么？）

缺点：

• 可空字段


• 潜在的额外类型列（可以跳过）


• 打破3NF（？）

请求利弊和个人意见。 ：）

编辑：我尝试通过使用与实际使用不同的实体来简化示例，所以建议改变模型不会真的帮助我即关注技术方面多于领域模型请。

解决方案

我的意见是，如果

  //然后根据用户选择，这些字段需要指定
 //（可以分解为单独的表）：
 {
  -  InternalAccountID（整数，带FK）
  -  InternalCompanyID（整数，带有FK）
} 
 
 //或者这些
 {
  -  ExternalAccountNumber（自由文本字符串）
  -  ExternalCompanyName（自由文本字符串）
  -  ExtraInformation（自由文本字符串）
 
  

始终为1：1，带有订单（即，您不能拥有3个帐户ID）然后把它当作一张桌子。要处理您的空问题，您可以添加一个名为InternalCustomer（boolean）或CustomerType（varChar）的列，您可以使用它们来定义内部或外部客户，以了解您应该查看的两组字段中的哪一个具体客户。

由于我们不知道完整使用此数据或整个数据库的架构，所以对此的任何响应都无法完全限定。

Should this be represented in the database as 1 table or 3 tables? I and my friend have different opinions about this so I'd like to see the general views on this. (Maybe it should be a vote for either solution?)

Create Table Order
// Basic fields of the table
 - ID (Primary key)
 - CustomerID  (integer, with a FK)
 - Quantity
 - ProductID  (integer, with a FK)

 // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

1 table approach:

Pros:

• performance (one insert as opposed to two, FK check, no joins)
• probably takes less space (the extra tables have overhead + indexes + extra ID field)
• one table as opposed to three
• hardly justifiable to have split out to new tables just for 2+3 fields (or what?)

Cons:

• Nullable fields
• Potentially extra "type" column (can be skipped)
• Breaks 3NF (?)

Pros and cons kindly requested as well as personal opinions. :)

EDIT: I tried simplifying the example by using different entities than I am actually using so any suggestions to altering the model wouldn't really help me. I.e. focus on the technical aspects more than the domain model please.

解决方案

My opinion would be that if

 // Then depending on user selection, either these fields need to be specified 
 // (could be factored out to a separate table):
 {
 - InternalAccountID (integer, with a FK)
 - InternalCompanyID (integer, with a FK)
 }

 // Or these (could be factored out to a separate table):
 {
 - ExternalAccountNumber (free text string)
 - ExternalCompanyName (free text string)
 - ExtraInformation (free text string)
 }

are always 1:1 with an order (i.e., you can't have 3 accountIDs), then leave it as one table. To take care of your null issue, you could add one more column called InternalCustomer (boolean) or CustomerType (varChar) that you could use to define an internal or external customer to know which of the two sets of fields you should look at for a specific customer.

Since we don't know the full use of this data or the schema for the entire DB, any response on this can't really be fully qualified.

这篇关于数据库规范化设计 - 单表或多表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！