单桥台还是有更好的方法？ [英] Single Bridge Table or is there a better way?

问题描述

我一直在试图找到一个答案，我不知道最好的方法来描述它。

I've been trying to find an answer for this and I don't know the best way to describe it.

基本上，我有3个源表具有独特的识别键。我们将这些表称为Skill1，Duty2，Custom3。它们不以任何方式彼此链接（容易），而不是它们是特定作业的属性的事实。我想把这些技能的资源（例如：一本书，一个url，一个课程ID）从现在的8个不同的表中将我称为Resource1-Resource8。这是为了允许将这些类型的资源的任何组合链接到所述3个技能表中包含的任何属性。

Basically, I have 3 source tables each with uniqueidentifier keys. We'll call these tables Skill1, Duty2, Custom3. They are not linked (easily) in any way with each other other than the fact that they are attributes of a particular job. I want to tie resources to these skills (eg: a book, a url, a course id) from what is now 8 different tables which I will call Resource1 - Resource8. This is to allow the linking of any combination of these types of resources to any of the attributes contained in said 3 "skill tables".

我想出了很多奇怪的设计，但我结算了以下：

I came up with a lot of odd designs, but I settled on the following:

表 - 列 - > FK

Skill1 - SkillUniqueId -> BridgeTable.AttributeUniqueId
Duty2 - DutyUniqueId -> BridgeTable.AttributeUniqueId
Custom3 - CustomUniqueId -> BridgeTable.AttributeUniqueId

BridgeTable - AttributeUniqueId, ResourceUniqueId, AttributeType, ResourceType

Resource1 - ResourceUniqueId -> BridgeTable.ResourceUniqueId
Resource2 - ResourceUniqueId -> BridgeTable.ResourceUniqueId
Resource3 - ResourceUniqueId -> BridgeTable.ResourceUniqueId
...etc.

这个简单设计的问题是我有一个桥表，链接3个属性表到8个资源表，我必须在存储过程（以及可能使用这些表的应用程序）的逻辑中使用AttributeType和ResourceType才能使用正确的表。

The "problem" with this simple design is that I have a bridge table linking 3 "attribute" tables to 8 resource tables and I have to use AttributeType and ResourceType in logic in the Stored Procedures (and possibly the application using these tables) in order to utilize the proper tables.

有没有更好的方式将这3个属性表链接到资源而不复制其他地方已经存在的数据？ （即：创建一个具有所有可能的属性组合的作业表，或将我的所有资源表整合到一个具有大量可空列的表中）

Is there a better way to link up these 3 attribute tables to the resources without duplicating data that already exists elsewhere? (ie: making a "job" table with all the possible combination of attributes, or mashing all my resource tables into one table with a lot of nullable columns)

推荐答案

在第三个正常表单数据库中，根据您所描述的内容，您将需要一个链接每个属性和每个可能的资源表的桥表。任何类型的快捷方式（例如您提出的）都会导致问题（例如您发现的内容），可以在 SQL Server Central ，或者也可能是Google的MUCK表。）

In a 3rd normal form database, based on what you've described you would need a bridge table linking each attribute and each possible resource table. Any kind of shortcut (such as what you've proposed) leads to problems (such as what you've found -- a good article on this subject can be found on SQL Server Central, or perhaps Google on "MUCK tables".)

可以将8个资源表中的数据修改为一个表，如：

Could the data in the 8 resource tables be revised into one table, something like:

ResourceUniqueId
ResourceType
ResourceName
ResourceEtc

可能有一个类型表来帮助定义/控制类型的资源？如果是这样，那么你只需要每个属性表的一个桥表。

with perhaps a "Type" table to help define/control the types of resources? If so, then you'd only need the one bridge table for each attribute table.

或者，也许一组类型 - 子类型表将有助于 - 顶级资源表，当前表格被配置为子类型。

Alternatively, perhaps a set of type-subtype tables would help -- top-level resource table, with the current tables getting configured as sub-types.

否则，受控的非规范化可能会有所帮助。创建组合所有资源的单个表，然后将数据从每个资源复制到其中。当然，非规范化的成本是，当（不是）资源更新时，你必须保持它们在多个地方同步，如果更新频繁，这可能是一个皇室噩梦。

Failing that, controlled denormalization might help. Create that single table combining all the resources, and then copy the data from each resource into it. Of course, the cost of denormalization is that when (not if) the resources are updated you'll have to keep them in synch in muptiple places, and if updates are frequent this can be a royal nightmare.

最终，如果您有复杂的数据，那么您将需要复杂的模型和代码来正确表示它。可以使用非正规化快捷方式，但是要确认它们是快捷方式，并且随着时间的推移，它们将需要额外的关怀，支持和维护。我听说过这个问题的最好的建议是首先产生完全正确的规范布局，然后才引入受控的非规范化来支持你的目标。

Ultimately, if you have complex data, then you will require complex models and code to properly represent it. Denormaliztion shortcuts can be taken, but recognize up front that they are shortcuts and they will require additional care, support, and maintenance over time. The best advice I've heard on this subject is to first produce the fully and properly normalized layout, and only then introduce controlled denormalizations to support your goals.

这篇关于单桥台还是有更好的方法？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！