标准化还是非标准化？ [英] Normalize or De-Normalize?

问题描述

大家好，

在我的winform应用程序中，我使用了一个包含很多字段的表（Table1）（我的意思是大约160个字段！），其中17个字段具有来自另一个表的外键（表2），所以当我想看到这17个字段的实际值而不是它们的关键时，我应该在Table1和Table2之间进行17次连接。



开另一方面，在我的应用程序中，我有一个允许用户将excel文件导入Table1的部分。在导入i之前，应该确保excel文件中17个字段中的每个字段的相关值已经存在于Table2中。然后。如果它存在从Table2获取其ID并且如果它不存在则首先在Table2中插入新值然后获取其ID。



所以现在我已经混淆了什么是在这种情况下最好的方法是？

标准化 Table1并使用表2中的外键，

OR

去标准化它有更快的性能？

Hi all,
In my winform application i use a table (Table1) with a lot of fields (I mean about 160 fields!), which 17 fields of them have foreign key from another table (Table2),so when i want to see the real value of these 17 fields but not the key of them i should make 17 joins between Table1 and Table2.

On the other hand in my application i have a part that allows user to import an excel file to Table1.Before importing i should make sure that the related value to each of that 17 fields from the excel file is already exists in Table2 or not.Then if it exists get its ID from Table2 and if it is not exists first insert that new value in Table2 and then get its ID.

So now i have confused what is the best way in this case?
Normalize Table1 and use foreign key from Table2,
OR
De-Normalize it to have faster performance?

推荐答案

好的。这是一个不错的问题。让我们从规范化部分开始。这是一个有用但理论上的东西。在日常情况下，你可以考虑跟随或不遵循它的实际原因。


我看不到你的具体型号，但如果只有一个值在另一个table（就像一个类别），你可以考虑保留两个表（类别表应该保存以传递值集），但是不是使用基于键的引用，而是将实际值放在引用表中 - 但是一定要保持一致。如果该字段中的数据不是那么大，这很有用。

让我们再举一个例子：发票。发票中包含可能随时间变化的买方地址，但发票后发票必须保持不变。因此，您必须确保即使合作伙伴的实际数据发生变化，与发票相关的存储数据也是相同的。但出于其他原因，您需要基于密钥的参考。因此，您可以采取混合方法：将所有合作伙伴数据保留在外部表中，将外键保留在发票表中 - 还要复制必须在发票表中保持原样的数据。

当你可以看到有几个注意事项 - 但你必须做出决定。



让我们来看看这160个字段。可能存在设计流程。很难分解这么高的数字。如果它们主要是可选字段（属性），您选择将所有可能性放在一个表中，您也应该考虑遵循（也结合上述方法）：使用XML字段及其中的那些属性;或者在表中保留真正唯一的字段，并使用1：N关系将属性外部化为除了引用之外仅包含名称 - 值对的表。我们假设你想存储一台电脑的数据。你会有一个处理器，主板，内存等字段。你可以按如下方式分解它：

Ok. This is a not a bad question. Let's start with the normalization part. It is a useful, but theoretical thing. In everyday situations you can consider following or not following it for practical reasons.

I can't see your concrete model, but if only a single value is in the other table (like a category), you can consider keeping both tables (the "category" table should be kept to deliver the set of values), but instead of using key based references, you put the actual value in the referring table - but be sure to be consistent. This is useful if the data in that field is not so large.
Let's take an other example: invoices. Invoices contains buyer address, that can change over time, but an invoice has to be kept unaltered after issuing it. So you have to assure, that the data stored related to the invoice is the same even if partner's actual data changes. But you need a key based reference for other reasons. So you can take a mixed approach: keep all partner data in the external table, keep the foreign key in invoices table - but also copy data that has to be kept intact in the invoice table.
As you can see there are several considerations - but you have to decide.

Let's take a look on those 160 fields. There might be a design flow. It is really rare that you can't decompose such a high number. If they are mostly optional fields (properties) you have chosen to put all possibilities in a single table, you should consider following too (also in combination with the above approach): use XML field and those properties in it; or keep the really unique fields in a table, and externalize the properties with an 1:N relation to a table that holds only name-value pairs in addition to the reference. Let's suppose you want to store the data of a pc. You would have a field for processor, motherboard, memory and so on. Than you could decompose it as follows:

COMPONENT_KINDS(ck_id, component_kind_name)<br />
PC (pc_id,....)<br />
PC_COMPONENTS (pcc_id, pc_id, ck_id, value)

因此，您可以为PC提供尽可能多的PC_COMPONENT记录。这就像将水平模型旋转到垂直模型一样。

So you could have only as many PC_COMPONENT records for a PC as many you need. It is like rotating a horizontal model to a vertical one.

这篇关于标准化还是非标准化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！