Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Expanding The Scope of Bridge Tables Expand / Collapse
Author
Message
Posted Monday, March 22, 2010 8:29 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, October 17, 2014 11:18 AM
Points: 22, Visits: 141
Comments posted to this topic are about the item Expanding The Scope of Bridge Tables
Post #887821
Posted Monday, March 22, 2010 9:32 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 6, 2012 11:24 PM
Points: 85, 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
Post #887827
Posted Monday, March 22, 2010 11:47 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 5, 2012 3:30 AM
Points: 138, 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
Post #887851
Posted Tuesday, March 23, 2010 12:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, August 27, 2013 4:57 AM
Points: 28, Visits: 124
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
Post #887869
Posted Tuesday, March 23, 2010 1:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, September 18, 2013 11:57 PM
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.
Post #887886
Posted Tuesday, March 23, 2010 3:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 12:38 PM
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.
Post #887923
Posted Tuesday, March 23, 2010 5:06 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, September 22, 2014 12:12 PM
Points: 86, Visits: 142
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
Post #887979
Posted Tuesday, March 23, 2010 7:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:43 PM
Points: 17, Visits: 55
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology
Post #888097
Posted Tuesday, March 23, 2010 8:36 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:07 PM
Points: 1,342, Visits: 1,716
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.
Post #888170
Posted Tuesday, March 23, 2010 8:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 12:38 PM
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.
Post #888187
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse