Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Expanding The Scope of Bridge Tables


Expanding The Scope of Bridge Tables

Author
Message
timclaason
timclaason
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 143
Comments posted to this topic are about the item Expanding The Scope of Bridge Tables
pcd.1
pcd.1
SSC Journeyman
SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)SSC Journeyman (87 reputation)

Group: General Forum Members
Points: 87 Visits: 209
Make sure you are aware of a concept called the 'Connection Trap' and 'Fan Out' issues. Had a customer once who had bridging tables that allowed them to see who had received promotional material, and who had responded to the promotional material, but could not determine which promotion elicited which response.

pcd
mar10br0
mar10br0
SSC-Enthusiastic
SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)SSC-Enthusiastic (160 reputation)

Group: General Forum Members
Points: 160 Visits: 351
Given the example of the article I tend to avoid building foreign keys to bridge-tables, but rather build a 3-way bridge for the training:
Table Name: Employee_FunctionalArea_TrainingCourse
Column Name Data Type
ID int (auto-increment, Primary Key)
FK_Employee int (relates to the Employee Table)
FK_FunctionalArea int (relates to the FunctionalArea Table)
FK_TrainingCourse int (relates to the TrainingCourse table)
TrainingDate DateTime

Then with business-logic I avoid "illogical" permutations (having an Employee do a training for an unrelated functional area).
This can give various answers while avoiding "going through" unnecessary tables (list all courses a certain employee is scheduled for regardless of functional area).
Another benefit is that this bridge-table is not reliant on the Employee_FunctionalArea bridge-table (an employee may at some point no longer be associated with a functional area, but you may still want to see which courses this employee has completed in the past related to any functional area). Or how about an employee broadening his scope by following a course outside his own functional areas to prepare for a future career-change?

My rule of thumb: a bridge-table should reference only "main" objects and not other bridge-tables. (but of course, as with any rule of thumb, you may decide otherwise in a certain situation ).

Marco
David Lean
David Lean
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 Visits: 129
Reading this article makes me very nervous. I've seen so many performance issues arise from approaches similar to this. While you didn't make a statement on it, most readers might think it is implied that the Primary Key is clustered & that no other constraints exist on the table.
So whether you can them Join Tables, Bridge Tables or something else. A couple of key things to consider.

1. Most likely this is a Many:Many Join. But each specific M:M join can only happen once. So you need to enforce uniqueness by either a) Making the Primary Key a Composite of the Foreign Keys. (improves joins from one side of the Many to the other) OR b) Have a Unique Constraint on the Foriegn Keys (this prevents the Join Table, exploding with Duplicate entries, who's INSERTS should've failed & been forced to be an UPDATE instead.

2. If your data is not permitted to be deleted. (unless archived), and you often do lots of reporting on it. You are likely to get better perf by carrying the "composite key (multiple FK's)" into the 3rd or 4th table as part of their key. This will let you create much more efficient joins & not require lots of tables to be joined into the query.

In short having an ID for each table, might not be the best plan. Especially if your schema lends itself into logical groupings. eg: 5 tables describe the Employee Training tables, another 6 describe Employee Performance etc. So you carry the FK's till you get 3 to 5 FK's making 1 Primary key before you generate a unique ID column which the other functional area joins with.
Hope this is useful
Dave
Vaibhav Jain
Vaibhav Jain
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 12
I agree with SSC-Enthusiastic views on this topic. I think its risky to reference a bridge table in another bridge table. It will narrow down the scope.
Taking the same example, If an employee is doing some training outside his functional area or if an employee had done some training but now he is not related to that functional area, what should we do in these cases.
paulgrahamster
paulgrahamster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
I have a couple of concerns about the approach you've taken with bridging a bridge table. Certainly in some instances it can make sense but by doing it the way you've proposed, you've built in business logic rules at the database level.

One such rule you've built in states that no two functional areas can have a particular training course in common. Because you're linking a completed training course through the Employee_FunctionalArea table, it follows that you either need to put multiple rows in that table to indicate where that training course can apply to different functional areas for the same employee or you assume that a single instance of training only applies to a single functional area.

Personally, I would have a separate table for the TrainingCourse and a bridge table (or link table or whatever you want to call it) for which training courses apply to which functional areas - no employee information involved. I would then have a bridge table linking simply employees to training courses.

That way, if an employee moves from Functional Area A to Functional Area B, we don't lose information about the training courses he's completed. If Functional Area B has some courses in common, the employee doesn't have to retake them just because he's moved to a new functional area.
Tom Wilson
Tom Wilson
SSC Journeyman
SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)SSC Journeyman (86 reputation)

Group: General Forum Members
Points: 86 Visits: 162
The basic idea for this topic is useful to brand-new database designeres - build a new table when there is a many-to-many relationship between two other tables. But note that we don't need a new name ("bridge table") for this concept. There is a long established discipline of data modeling, and a table such as this already has names such as associative entity, using entity-relationship terminology. See http://en.wikipedia.org/wiki/Associative_Entities
Ed-997158
Ed-997158
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 55
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology
sknox
sknox
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2138 Visits: 2749
paulgrahamster (3/23/2010)
One such rule you've built in states that no two functional areas can have a particular training course in common. Because you're linking a completed training course through the Employee_FunctionalArea table, it follows that you either need to put multiple rows in that table to indicate where that training course can apply to different functional areas for the same employee or you assume that a single instance of training only applies to a single functional area.

Personally, I would have a separate table for the TrainingCourse and a bridge table (or link table or whatever you want to call it) for which training courses apply to which functional areas - no employee information involved. I would then have a bridge table linking simply employees to training courses.

That way, if an employee moves from Functional Area A to Functional Area B, we don't lose information about the training courses he's completed. If Functional Area B has some courses in common, the employee doesn't have to retake them just because he's moved to a new functional area.

I agree, with one change: in the employee-training bridge table, I would still include functional area information. Why? Because sometimes trainers will adapt their material based on their audience, without going through "the bother" of creating another class for it*. For example, suppose that an employee moves from cashier to store manager, and there is Loss Prevention training that they went through as a cashier shortly before being promoted. The date of training and date of promotion may be close enough to cause confusion, but if you can see that they went through the training as a cashier, you can decide that they may have missed some management-specific pieces, and ask them to retrain on it (nicely, of course.)

*Yes, I know it should be a different class. But this is the real world we're talking about. You can adapt your database to the real world, or you can try to alter the real world to suit your database. Guess which one might work out.
paulgrahamster
paulgrahamster
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 12
sknox (3/23/2010)

I agree, with one change: in the employee-training bridge table, I would still include functional area information. Why? Because sometimes trainers will adapt their material based on their audience, without going through "the bother" of creating another class for it*. For example, suppose that an employee moves from cashier to store manager, and there is Loss Prevention training that they went through as a cashier shortly before being promoted. The date of training and date of promotion may be close enough to cause confusion, but if you can see that they went through the training as a cashier, you can decide that they may have missed some management-specific pieces, and ask them to retrain on it (nicely, of course.)

*Yes, I know it should be a different class. But this is the real world we're talking about. You can adapt your database to the real world, or you can try to alter the real world to suit your database. Guess which one might work out.


Fair enough with the suggestion of "it was the 'cashier' flavour of the 'loss prevention' course". The point remains, though, that the employee should be linked directly to a course, not indirectly through FunctionalArea_TrainingCourse.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search