不必要的冗余与表 [英] Unnecessary Redundancy with Tables

问题描述

我的项目列出如下：这只是一个总结。但是我使用Detail表格显示的方法来代表一种继承类型，因为Item和Downloadable将是完全相同的，除了每个都将有一些附加的字段相关只给他们

我的问题在于这种设计模式。这样的事情在我们的项目中出现了很多次，有没有更聪明的方法来处理？我基本上需要尽可能规范化表格。我对数据库非常新鲜，所以这对我来说都是非常混乱的。

共有5个项目。奖励，项目，购买，令牌和下载。它们都非常非常相似，只是每个数据都只与本身有关。我试图使用一个声明字段（如枚举器'Type'字段）与可空列相结合，但我被告知这是一个坏的方法。我所做的是将所有类似的东西放在一个表中，然后每个类型都有自己的表，它引用了'base'表中的列。

关系或交点出现问题。将所有这些返回给客户。每个类型需要大约2个额外的表，以将所有数据正确连接在一起 - 因此，我的数据库正在增长非常大，非常大。这种行为有更聪明的做法吗？

 项目
 ID | GUID 
名称| varchar（64）
 
产品
 ID | GUID 
名称| varchar（64）
商店| GUID [FK] 
详细信息| GUID [FK] 
 
可下载
 ID | GUID 
名称| varchar（64）
 Url | nvarchar（2048）
详细信息| GUID [FK] 
 
详细信息
 ID | GUID 
价格| decimal 
说明|文本
 
外设[JUNCTION] 
 ID | GUID 
详细信息| GUID [FK] 
 
商店
 
 ID | GUID 
地址| GUID 
 
地址
 ID | GUID 
名称| nvarchar（64）
州| int [FK] 
 ZipCode | int 
地址| nvarchar（64）
 
 
状态
 ID | int 
名称| varchar（32）
  

解决方案

这种继承总是关系数据库的一点点技巧。你所拥有的是一种方法，它是传统问题的最传统方法。你最终做了很多交叉的表，但这可能很好。

另一种方法是使用一些非规范化并将表折叠到一个表中。包含一个类型字段，它表示项目的类型，然后具有该表中所有字段的并集。所以你会有一张表如

  ID | GUID 
类型| GUID [FK] 
名称| nvarchar（64）
州| int [FK] 
 ZipCode | int 
地址| nvarchar（64）
名称| varchar（64）
 Url | nvarchar（2048）
商店| GUID [FK] 
详细信息| GUID [FK] 
 ... 
  

这意味着你有一堆空字段你的桌子

您还可以采取更分散的方法，并构建您的表，如

 项目：
 ID | GUID 
 
 ItemPropertyType：
 ID | GUID 
名称| nvarchar（50）
 
 ItemProperty：
 ID | GUID 
 ItemID | GUID [FK] 
 ItemPropertyTypeID | GUID 
 charValue | varchar（64）
  

每个项目属性引用一个项目。要构建一个项目，你只需收集它拥有的ItemProperties。如果您想查找名称为bill的所有项目，那么您可以执行

 从ItemProperties ip，ItemPropertyTypes ipn中选择ItemID ipn.ID = ip.ItemPropertyTypeID和ipt.Name ='Name'和ip.charValue ='bill'
  

Jeff实际上对此主题有所了解 http： //www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html

My items are listed as follows; This is just a summary of course. But I'm using a method shown for the "Detail" table to represent a type of 'inheritence', so to speak - since "Item" and "Downloadable" are going to be identical except that each will have a few additional fields relevant only to them.

My question is in this design pattern. This sort of thing appears many, many times in our projects - is there a more intelligent way to handle it? I basically need to normalize the tables as much as possible. I'm extremely new to databases and so this is all very confusing to me.

There are 5 items. Awards, Items, Purchases, Tokens, and Downloads. They are all very, very similar, except each has a few pieces of data relevant only to itself. I've tried to use a declaration field (like an enumerator 'Type' field) in conjunction with nullable columns, but I was told that is a bad approach. What I have done is take everything similar and place it in a single table, and then each type has its own table that references a column in the 'base' table.

The problem occurs with relationships, or junctions. Linking all of these back to a customer. Each type takes around 2 additional tables to properly junction all of the data together- and as such, my database is growing very, very large. Is there a smarter practice for this kind of behavior?

Item
ID      | GUID
Name      | varchar(64)

Product
ID      | GUID
Name      | varchar(64)
Store     | GUID [ FK ]
Details  | GUID [FK]

Downloadable
ID      | GUID
Name      | varchar(64)
Url    | nvarchar(2048)
Details | GUID [FK]

Details
ID           | GUID
Price         | decimal
Description | text

Peripherals [ JUNCTION ]
ID      | GUID
Detail      | GUID [FK]

Store

ID      | GUID
Addresses   | GUID

Addresses
ID      | GUID
Name        | nvarchar(64)
State    | int [FK]
ZipCode | int
Address | nvarchar(64)


State
ID      | int
Name        | varchar(32)

解决方案

This sort of inheritance is always a bit trick with relational databases. What you have is one approach and it is the most traditional approach to the problem. You end up doing a lot of crossing of tables, but that might be just fine.

Another approach is to employ some denormalization and collapse the tables into one table. Include a type field which represents the type of the item and then have the union of the fields all in that table. So you would have a table like

ID  | GUID
Type | GUID [FK]
Name        | nvarchar(64)
State    | int [FK]
ZipCode | int
Address | nvarchar(64)
Name      | varchar(64)
Url    | nvarchar(2048)
Store     | GUID [ FK ]
Details  | GUID [FK]
...

This means you have a bunch of empty fields in your table.

You could also take a more fragmented approach and construct your tables like

Item:
ID | GUID

ItemPropertyType:
ID | GUID
Name | nvarchar(50)

ItemProperty:
ID | GUID
ItemID | GUID [FK]
ItemPropertyTypeID | GUID
charValue | varchar(64)

Each item property references an item. To construct an item you simply gather up the ItemProperties which it owns. If you wanted to find all items where Name was 'bill' then you could do

select ItemID from ItemProperties ip, ItemPropertyTypes ipn where ipn.ID = ip.ItemPropertyTypeID and ipt.Name='Name' and ip.charValue='bill'

Jeff actually blogged a bit about this topic http://www.codinghorror.com/blog/2008/07/maybe-normalizing-isnt-normal.html

这篇关于不必要的冗余与表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！