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 Tuesday, March 23, 2010 11:22 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Saturday, June 8, 2013 9:40 AM
Points: 142, Visits: 286
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"!
Post #888379
Posted Tuesday, March 23, 2010 11:29 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 9:44 PM
Points: 21,187, Visits: 14,879
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #888389
Posted Tuesday, March 23, 2010 12:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 11, 2012 9:31 AM
Points: 5, 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
Post #888406
Posted Tuesday, March 23, 2010 12:13 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 17, 2014 4:04 PM
Points: 69, Visits: 386
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
Post #888416
Posted Tuesday, March 23, 2010 12:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, May 5, 2014 1:57 PM
Points: 545, Visits: 643
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.
Post #888424
Posted Tuesday, March 23, 2010 3:51 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:12 PM
Points: 436, Visits: 3,241
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.


David
Post #888561
Posted Tuesday, March 23, 2010 6:24 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 1:22 PM
Points: 86, Visits: 139
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.
Post #888607
Posted Tuesday, March 23, 2010 7:35 PM
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
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.
Post #888641
Posted Wednesday, March 24, 2010 12:39 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:12 PM
Points: 436, Visits: 3,241
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.


David
Post #888710
Posted Wednesday, March 24, 2010 10:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 6:59 AM
Points: 87, Visits: 273
.../...
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!


+1
Post #889104
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse