March 22, 2010 at 8:29 pm
Comments posted to this topic are about the item Expanding The Scope of Bridge Tables
March 22, 2010 at 9:32 pm
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
March 22, 2010 at 11:47 pm
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 NameData 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
March 23, 2010 at 12:57 am
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
March 23, 2010 at 1:58 am
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.
March 23, 2010 at 3:36 am
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.
March 23, 2010 at 5:06 am
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
March 23, 2010 at 7:14 am
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology
March 23, 2010 at 8:36 am
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.
March 23, 2010 at 8:54 am
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.
March 23, 2010 at 9:03 am
Tom Wilson (3/23/2010)
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
Gee, I always thought it was called an "intersection"! It's probably more useful to use slang like this when explaining architecture to non-technical people, than using "associative entity".
March 23, 2010 at 9:08 am
David Korb (3/23/2010)
Tom Wilson (3/23/2010)
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_EntitiesGee, I always thought it was called an "intersection"! It's probably more useful to use slang like this when explaining architecture to non-technical people, than using "associative entity".
Silly me. I thought analysts, designers and developers were technical people.
March 23, 2010 at 9:14 am
David Korb (3/23/2010)
Tom Wilson (3/23/2010)
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_EntitiesGee, I always thought it was called an "intersection"! It's probably more useful to use slang like this when explaining architecture to non-technical people, than using "associative entity".
Tim great article, better title then the last. If you had read his previous article and went throught the discussions you'd see that there are many names for the same concept. The term "bridge table" has been around as long or longer than the term "associative entity". A scientist will call a dog a canine. Most of us when we see one walk down the street don't say, "Hey look at that cute canine".
The thing I like most about the articles at ssc is the discussions on the articles. As much or more can be learned from the discussions than from the actual articles. I do like the wikipedia link.
---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum
March 23, 2010 at 9:17 am
Ed-997158 (3/23/2010)
David Korb (3/23/2010)
Tom Wilson (3/23/2010)
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_EntitiesGee, I always thought it was called an "intersection"! It's probably more useful to use slang like this when explaining architecture to non-technical people, than using "associative entity".
Silly me. I thought analysts, designers and developers were technical people.
I was referring to the use of technical language in a broader context than SSC, such as the business managers in our companies. You know, the people we have difficulty explaining things to some times!
March 23, 2010 at 9:22 am
First of all, while I have no issues with the Employee_FunctionalArea construct used in this article I wish we could all reach an agreement on what to call a table used to resolve many-to-many relationships. I've heard "bridge" table, "cross-walk", "cross-reference", and "link" table to name a few. As one poster mentioned, in modeling terminology these are often referred to as "associative", "intersectional", "resolution" or "junction" entities depending on whose book you read.
From a design point of view I think I might take a simpler approach to solving the training issue. We know that because "the training item that the employee needs to be trained on is specific to the functional area in which they work" there is a one-to-one relationship between functional_area and training_course so I would add the FK_TrainingCourse to the FunctionalArea table.
Table Name: FunctionalArea
Column Name Data Type
ID int (auto-increment, Primary Key)
Name varchar(50)
FK_TrainingCourse int (relates to the TrainingCourse table)
I would then add TrainingDate to the bridge table. It is, after all, an attribute of the Employee/FunctionalArea relationship.
Table Name: Employee_FunctionalArea
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)
TrainingDate DateTime
This way you don't need the Employee_FunctionalArea_TrainingCourse table at all.
Just my two cents.
"Beliefs" get in the way of learning.
Viewing 15 posts - 1 through 15 (of 34 total)
You must be logged in to reply to this topic. Login to reply