Single Bridge Table or is there a better way?

Question

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

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:

Table - Column -> 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.

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.

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)

Answer 1

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

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.

Answer 2