SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Timothy Claason / Expanding The Scope of Bridge Tables / Latest Posts

RE: Expanding The Scope of Bridge Tables

Richard Gibbins — Fri, 04 Jun 2010 11:57:40 GMT

[quote][b]Tom.Thomson (4/5/2010)[/b][hr][quote][b]Ed-997158 (4/5/2010)[/b][hr]I found "The world will come to an end, but music and love will endure" as the translation for your second tag line, but might I request a translation for the first? Is it something about assumptions?Cheers,Ed[/quote]literal: [do] not pick me up without I fall but if [I do] fall pick [me] upmeaning: don't correct me unless I am wrong, but if I am wrong do correct meI much prefer "survive" to "endure" - mairidh means roughly "will continue to live" so I think "endure" is a poor translation.[/quote]I think the sense of endure as used the translation is "go on or last forever". For me, that makes the sentiment more powerful than survive. On the other hand, I don't know gaelic at all so I can't attest to how well it reflects the source. Isn't translating fun?

RE: Expanding The Scope of Bridge Tables

L' Eomot Inversé — Mon, 05 Apr 2010 20:42:47 GMT

[quote][b]Ed-997158 (4/5/2010)[/b][hr]I found "The world will come to an end, but music and love will endure" as the translation for your second tag line, but might I request a translation for the first? Is it something about assumptions?Cheers,Ed[/quote]literal: [do] not pick me up without I fall but if [I do] fall pick [me] upmeaning: don't correct me unless I am wrong, but if I am wrong do correct meNot really about assumptions, but can certainly be used in the context of making valid or invalid assumptions.I suspect the translation you found for the other line was for a slightly different version: the difference is that gaol and ceòl (love and music) are in the opposite order. A quick check using google shows 2440 occurrences of the phrase (in Gaelic, I haven't searched for the English) with "love and music" and only 544 with "music and love", most of which I suspect come from a misquotation in one pop group's lyrics or from a similar misquotation in a learner's post (which may itself have been derived from those lyrics) to the gaidhlig-a discussion list. I much prefer "survive" to "endure" - mairidh means roughly "will continue to live" so I think "endure" is a poor translation. But "endure" is much better than the amazing gaffe "be early": we use "nach maireann" (not living) where English uses "late", and a Japanese student who didn't understand what "late" meant in a phrase like "the late Mr Shakespeare" did manage (correctly) to connect mairidh with maireann, with the inevitable unfortunate result.

RE: Expanding The Scope of Bridge Tables

Ed-997158 — Mon, 05 Apr 2010 08:09:13 GMT

[quote][b]Tom.Thomson (4/4/2010)[/b][hr][/quote]I found "The world will come to an end, but music and love will endure" as the translation for your second tag line, but might I request a translation for the first? Is it something about assumptions?Cheers,Ed

RE: Expanding The Scope of Bridge Tables

L' Eomot Inversé — Sun, 04 Apr 2010 09:34:43 GMT

RE: Expanding The Scope of Bridge Tables

sjsubscribe — Wed, 24 Mar 2010 10:19:21 GMT

[quote][b]David Korb (3/23/2010)[/b][hr][quote]I have to vote no on "bridge" since I've seen it used with respect to data migration as well as company merger activities.Vote "NO" on "Bridge"! ;-)[/quote]Didn't we already vote against the bridge to nowhere in the last election.:-)But I agree. This bridge needless abstraction to understanding fundamental relational techniques. Just getting past vendor-specific terminologies is enough of a hurdle. Why add more?

RE: Expanding The Scope of Bridge Tables

rot-717018 — Wed, 24 Mar 2010 10:15:05 GMT

[quote].../...As someone above noted, I have never heard bridge table used, but understood the purpose right away. I usually said "relationship table", doesn't roll off the tongue as easily! :-D[/quote]+1

RE: Expanding The Scope of Bridge Tables

sqlvogel — Wed, 24 Mar 2010 00:39:25 GMT

[quote][b]David Lean (3/23/2010)[/b][hr]Too often lazy or inexperienced DB designers automatically create an Identity Column for PK of every table they create. And they fail to ensure the uniqueness of the FK pairs. This mistake is so common that over the past 20 years I've seen this as the root cause of a poor performing DB solutions at least 3 times each year. In all cases perf at least doubles just by correcting this mistake, In one case removing it & the massive number of duplicate relationship rows it permitted, resulted in an 80,000 times improvement in perf. Which on a 9 * Web Server to 2 * 8 way SQL system, means everything pegged & losing business, to everything idle & taking 4x number of orders.[/quote]Quite true, but identifying and enforcing the business key in a table with a surrogate key should apply equally to [i]every[/i] table, not just those with more than one foreign key. The problems you mention commonly apply to other tables as well.

RE: Expanding The Scope of Bridge Tables

David Lean — Tue, 23 Mar 2010 19:35:37 GMT

[quote][b]David Portas (3/23/2010)[/b][hr][quote][b]Ed-997158 (3/23/2010)[/b][hr]Thanks. I was just going to ask if "bridge table" didn't already have a different terminology[/quote]"Associative Entity" is semantic modelling terminology and not a relational database term. I've never found much use in the idea of singling out tables in this way. As far as I see it, a "bridge" or "associative" table can be any table with more than one foreign key. But why do we need a special name for tables with more than one foreign key?Fundamentally there is just one type of relational table (i.e. a table that properly represents a relation). Any such table represents an N-ary relationship between its attributes and "associative" tables are not a special case in any important respect.[/quote]I take your point on the variety of names for the same concept. But I do believe it useful to have a special name for a table whose sole reason for existance is to support a Many to Many join. This table is different because it describes the "Relationship" & not a "Business Entity" like most other tables. Ideally the name should alert you to the fact that the FK's are special, as they most likely represent the Composite Primary Key for the table. AND you should think very carefully before creating an Alternate Key for the Primary Key. Too often lazy or inexperienced DB designers automatically create an Identity Column for PK of every table they create. And they fail to ensure the uniqueness of the FK pairs. This mistake is so common that over the past 20 years I've seen this as the root cause of a poor performing DB solutions at least 3 times each year. In all cases perf at least doubles just by correcting this mistake, In one case removing it & the massive number of duplicate relationship rows it permitted, resulted in an 80,000 times improvement in perf. Which on a 9 * Web Server to 2 * 8 way SQL system, means everything pegged & losing business, to everything idle & taking 4x number of orders.

RE: Expanding The Scope of Bridge Tables

Tom Wilson — Tue, 23 Mar 2010 18:24:18 GMT

RE: Expanding The Scope of Bridge Tables

sqlvogel — Tue, 23 Mar 2010 15:51:14 GMT

[quote][b]Ed-997158 (3/23/2010)[/b][hr]Thanks. I was just going to ask if "bridge table" didn't already have a different terminology[/quote]"Associative Entity" is semantic modelling terminology and not a relational database term. I've never found much use in the idea of singling out tables in this way. As far as I see it, a "bridge" or "associative" table can be any table with more than one foreign key. But why do we need a special name for tables with more than one foreign key?Fundamentally there is just one type of relational table (i.e. a table that properly represents a relation). Any such table represents an N-ary relationship between its attributes and "associative" tables are not a special case in any important respect.

RE: Expanding The Scope of Bridge Tables

Robert Frasca — Tue, 23 Mar 2010 12:19:22 GMT

[quote][b]paulgrahamster (3/23/2010)[/b][hr][quote][b]Robert Frasca (3/23/2010)[/b][hr]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: FunctionalAreaColumn Name Data Type ID int (auto-increment, Primary Key) Name varchar(50) FK_TrainingCourse int (relates to the TrainingCourse table)[/quote]I wouldn't agree with that at all.If a training course is specific to a functional area and can never relate to more than one, you'd obviously put a foreign key in the training course back to the functional area.What you're describing is that each functional area has only one training course but that training course could potentially cover more than one functional area.The point of the bridge table (naming arguments aside) is to describe a many-to-many relationship. Using a foreign key in the way you have describes a one-to-many (course-to-functional area) relationship.[/quote]I guess I don't understand your problem with the fact that a training course could potentially cover more than one functional area. That's an implementation issue and has nothing to do with the model. All that matters is that the FunctionalArea can only be associated with one TrainingCourse. It's immaterial if two FunctionalArea rows point at the same TrainingCourse row. There's still only one TrainingCourse per FunctionalArea. That's like being annoyed if two addresses point at the same State row. It's still a one-to-one relationship between address and state. For each address there is one, and only one, state.

RE: Expanding The Scope of Bridge Tables

BowlOfCereal — Tue, 23 Mar 2010 12:13:21 GMT

[quote][b]Tom Wilson (3/23/2010)[/b][hr]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[/quote]Not to quibble a minor point, but "bridge table" is not a new name for this concept. It's a commonly accepted term, widely used at least in Kimball-style data warehousing. As you point out, this idea already goes by many names, but bridge table is the one I'm used to. See http://en.wikipedia.org/wiki/Bridge_Table

RE: Expanding The Scope of Bridge Tables

SamElston — Tue, 23 Mar 2010 12:01:16 GMT

I agree with a lot of the discussion comments.Agree with the "bridge" table concept (nothing new there).But I must say it seems like the second half of the article added little value. (Sorry - not trying to be a rude). But if I did not know better, and tried to build upon all the advice in this artilce, I think it would not be a good thing (sorry again). As others also stated, I don't like the design of the second bridge table - it does not seem like a good idea. However, I will keeping thinking about it to see where it may apply in my workI'm glad that the article explicitly tells the reader to check out the comments/Discussion - as there is some good info to balance our the article.Sorry if misunderstood some of the article

RE: Expanding The Scope of Bridge Tables

SQLRNNR — Tue, 23 Mar 2010 11:29:04 GMT

Thanks for the article.This has been an interesting discussion on the article. There are some good design tips throughout the discussion - which point out the need to fully understand an environment when creating a design.

RE: Expanding The Scope of Bridge Tables

David Korb — Tue, 23 Mar 2010 11:22:51 GMT

[quote][b]JJ B (3/23/2010)[/b][hr]I want to weigh in on the jargon discussion.I don't think I've heard the term "bridge" table before, and yet without even reading the article, I knew instantly what the author was talking about. I think that makes it a great term to use. It's clear and easy to understand. As a previous poster pointed out, there is usually no problem having a simple, single-word lay person's description of something and also a longer, more technical phrase. Since this is an entry-level article, the term used seems appropriate. If the article had had a paragraph discussing the several terms used for the concept, it would have been fine, but not necessary.[/quote]I have to vote no on "bridge" since I've seen it used with respect to data migration as well as company merger activities.Vote "NO" on "Bridge"! ;-)

RE: Expanding The Scope of Bridge Tables

Kit Brandner — Tue, 23 Mar 2010 11:22:50 GMT

Article and discussion was a good read, but I couldn't really agree with the schema used to get the point across. Especially at the end, where you start de-normalizing the database structure just so developers can write quicker, easier queries. This should never be a design goal in my opinion. I would much rather design a database towards scalability, and what the application requires, then build views/procedures for developers that don't quite understand the language. Teaching the opposite breeds over-bloated, thrown-together databases that usually end in a re-write and a major headache.As someone above noted, I have never heard bridge table used, but understood the purpose right away. I usually said "relationship table", doesn't roll off the tongue as easily! :-D

RE: Expanding The Scope of Bridge Tables

dwinters 67194 — Tue, 23 Mar 2010 11:17:01 GMT

What I like about this from a consulting persepective is the definite increase in MCD.I've seen this type of design pattern soooo... many times, in many different venues.I'm currently working with a health care software company that uses many "bridge tables" in their design.It has resulted in a total MESS and logarithmic increase in complexity and poor performance. The wonderful part is the increase in MCD.So, I'm deeply involved in working with them to take a hard look at the orginal model and issues that cuased them to go down this path. The main discovery is that the orginal design(about 5 years ago)didn't fully model and understand the fundamental entities and the relationships between them.Over time the incomplete design has been patched and "fixed" with M-to-M bridges, procs, triggers ..et al.Wonderful stuff!Oh forgot to explain what MCD means...MCD = More Consulting Dollars:-D

RE: Expanding The Scope of Bridge Tables

paulgrahamster — Tue, 23 Mar 2010 10:32:28 GMT

[quote][b]Robert Frasca (3/23/2010)[/b][hr]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: FunctionalAreaColumn Name Data Type ID int (auto-increment, Primary Key) Name varchar(50) FK_TrainingCourse int (relates to the TrainingCourse table)[/quote]I wouldn't agree with that at all.If a training course is specific to a functional area and can never relate to more than one, you'd obviously put a foreign key in the training course back to the functional area.What you're describing is that each functional area has only one training course but that training course could potentially cover more than one functional area.The point of the bridge table (naming arguments aside) is to describe a many-to-many relationship. Using a foreign key in the way you have describes a one-to-many (course-to-functional area) relationship.

RE: Expanding The Scope of Bridge Tables

JJ B — Tue, 23 Mar 2010 09:33:16 GMT

I want to weigh in on the jargon discussion.I don't think I've heard the term "bridge" table before, and yet without even reading the article, I knew instantly what the author was talking about. I think that makes it a great term to use. It's clear and easy to understand. As a previous poster pointed out, there is usually no problem having a simple, single-word lay person's description of something and also a longer, more technical phrase. Since this is an entry-level article, the term used seems appropriate. If the article had had a paragraph discussing the several terms used for the concept, it would have been fine, but not necessary.

RE: Expanding The Scope of Bridge Tables

Ed-997158 — Tue, 23 Mar 2010 09:32:21 GMT

[quote][b]David Korb (3/23/2010)[/b][hr][quote][b]Ed-997158 (3/23/2010)[/b][hr][quote][b]David Korb (3/23/2010)[/b][hr][quote][b]Tom Wilson (3/23/2010)[/b][hr]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[/quote]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".[/quote]Silly me. I thought analysts, designers and developers were technical people.[/quote]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![/quote]Ah, the two syllable or less people. Point taken.

RE: Expanding The Scope of Bridge Tables

Robert Frasca — Tue, 23 Mar 2010 09:22:25 GMT

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: FunctionalAreaColumn 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_FunctionalAreaColumn 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.

RE: Expanding The Scope of Bridge Tables

David Korb — Tue, 23 Mar 2010 09:17:55 GMT

[quote][b]Ed-997158 (3/23/2010)[/b][hr][quote][b]David Korb (3/23/2010)[/b][hr][quote][b]Tom Wilson (3/23/2010)[/b][hr]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[/quote]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".[/quote]Silly me. I thought analysts, designers and developers were technical people.[/quote]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!

RE: Expanding The Scope of Bridge Tables

Trey Staker — Tue, 23 Mar 2010 09:14:17 GMT

[quote][b]David Korb (3/23/2010)[/b][hr][quote][b]Tom Wilson (3/23/2010)[/b][hr]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[/quote]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".[/quote]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.

RE: Expanding The Scope of Bridge Tables

Ed-997158 — Tue, 23 Mar 2010 09:08:11 GMT

RE: Expanding The Scope of Bridge Tables

David Korb — Tue, 23 Mar 2010 09:03:34 GMT

[quote][b]Tom Wilson (3/23/2010)[/b][hr]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[/quote]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".

RE: Expanding The Scope of Bridge Tables

paulgrahamster — Tue, 23 Mar 2010 08:54:30 GMT

[quote][b]sknox (3/23/2010)[/b][hr]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 [b]should[/b] 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.[/quote]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.

RE: Expanding The Scope of Bridge Tables

sknox — Tue, 23 Mar 2010 08:36:27 GMT

[quote][b]paulgrahamster (3/23/2010)[/b][hr]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.[/quote]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 [b]should[/b] 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.

RE: Expanding The Scope of Bridge Tables

Ed-997158 — Tue, 23 Mar 2010 07:14:33 GMT

Thanks. I was just going to ask if "bridge table" didn't already have a different terminology

RE: Expanding The Scope of Bridge Tables

Tom Wilson — Tue, 23 Mar 2010 05:06:18 GMT

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

RE: Expanding The Scope of Bridge Tables

paulgrahamster — Tue, 23 Mar 2010 03:36:01 GMT

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.

RE: Expanding The Scope of Bridge Tables

Vaibhav Jain — Tue, 23 Mar 2010 01:58:25 GMT

I agree with [b]SSC-Enthusiastic[/b] 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.

RE: Expanding The Scope of Bridge Tables

David Lean — Tue, 23 Mar 2010 00:57:07 GMT

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) [b]Making the Primary Key a Composite of the Foreign Keys[/b]. (improves joins from one side of the Many to the other) OR b) [b]Have a Unique Constraint on the Foriegn Keys[/b] (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

RE: Expanding The Scope of Bridge Tables

mar10br0 — Mon, 22 Mar 2010 23:47:28 GMT

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:[b]Table Name: Employee_FunctionalArea_TrainingCourse[/b][b]Column Name Data Type[/b]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 DateTimeThen 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

RE: Expanding The Scope of Bridge Tables

pcd.1 — Mon, 22 Mar 2010 21:32:06 GMT

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

Expanding The Scope of Bridge Tables

timclaason — Mon, 22 Mar 2010 20:29:13 GMT

Comments posted to this topic are about the item [B]Expanding The Scope of Bridge Tables[/B]