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
Say Hey Kid
Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)Say Hey Kid (661 reputation)

Group: General Forum Members
Points: 661 Visits: 311
JJ B (3/23/2010)
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.


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"! ;-)
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)SSC Guru (69K reputation)

Group: General Forum Members
Points: 69137 Visits: 18570
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.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw

SamElston
SamElston
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 77
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 work

I'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
BowlOfCereal
BowlOfCereal
Mr or Mrs. 500
Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)Mr or Mrs. 500 (572 reputation)

Group: General Forum Members
Points: 572 Visits: 469
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


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
Robert Frasca
Robert Frasca
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: 1601 Visits: 798
paulgrahamster (3/23/2010)
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.


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.

"Beliefs" get in the way of learning.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2196 Visits: 3708
Ed-997158 (3/23/2010)
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology


"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.
Tom Wilson
Tom Wilson
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 162
David Portas (3/23/2010)
Ed-997158 (3/23/2010)
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology


"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.


I don't want to get into a modeling food fight, but don't semantics matter? There are some definite classes of relationships (design patterns) that are modeled in a relational database; the many-to-many betweeen tables, which cannot be handled in first normal form and requires the bridge/associative table, is one of these. Recognizing these design patterns and saying "oh, this is one of those and here's how you solve" is important to avoid re-inventing the wheel each time you design a new part of a database.
David Lean
David Lean
SSC Veteran
SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)SSC Veteran (206 reputation)

Group: General Forum Members
Points: 206 Visits: 129
David Portas (3/23/2010)
Ed-997158 (3/23/2010)
Thanks. I was just going to ask if "bridge table" didn't already have a different terminology


"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.


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.
sqlvogel
sqlvogel
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2196 Visits: 3708
David Lean (3/23/2010)
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.


Quite true, but identifying and enforcing the business key in a table with a surrogate key should apply equally to every table, not just those with more than one foreign key. The problems you mention commonly apply to other tables as well.
rot-717018
rot-717018
Mr or Mrs. 500
Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)Mr or Mrs. 500 (537 reputation)

Group: General Forum Members
Points: 537 Visits: 444
.../...
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


+1
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