SQL Clone
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
David Korb
David Korb
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 311
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".
Ed-997158
Ed-997158
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 55
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_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".


Silly me. I thought analysts, designers and developers were technical people.
Trey Staker
Trey Staker
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1872 Visits: 2788
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_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".


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
David Korb
David Korb
SSC Eights!
SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)SSC Eights! (969 reputation)

Group: General Forum Members
Points: 969 Visits: 311
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_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".


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!
Robert Frasca
Robert Frasca
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2333 Visits: 801
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.
Ed-997158
Ed-997158
SSC-Enthusiastic
SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)SSC-Enthusiastic (155 reputation)

Group: General Forum Members
Points: 155 Visits: 55
David Korb (3/23/2010)
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_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".


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!


Ah, the two syllable or less people. Point taken.
JJ B
JJ B
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1643 Visits: 2861
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.
paulgrahamster
paulgrahamster
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 12
Robert Frasca (3/23/2010)


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 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.
dwinters 67194
dwinters 67194
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 11
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
Kit Brandner
Kit Brandner
SSC Rookie
SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)SSC Rookie (35 reputation)

Group: General Forum Members
Points: 35 Visits: 196
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
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