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 9:03 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:38 AM
Points: 142, Visits: 287
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".
Post #888198
Posted Tuesday, March 23, 2010 9:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:43 PM
Points: 17, 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.
Post #888206
Posted Tuesday, March 23, 2010 9:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, November 24, 2014 3:43 PM
Points: 1,142, Visits: 2,691
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
Post #888215
Posted Tuesday, March 23, 2010 9:17 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 8:38 AM
Points: 142, Visits: 287
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!
Post #888220
Posted Tuesday, March 23, 2010 9:22 AM
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: Wednesday, November 19, 2014 12:30 PM
Points: 554, Visits: 672
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.
Post #888221
Posted Tuesday, March 23, 2010 9:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 9, 2013 2:43 PM
Points: 17, 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.
Post #888241
Posted Tuesday, March 23, 2010 9:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 14, 2014 10:29 AM
Points: 266, Visits: 2,610
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.
Post #888244
Posted Tuesday, March 23, 2010 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 5, 2010 12:38 PM
Points: 5, 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.
Post #888310
Posted Tuesday, March 23, 2010 11:17 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, October 12, 2010 11:33 AM
Points: 2, 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
Post #888370
Posted Tuesday, March 23, 2010 11:22 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, May 25, 2010 12:06 PM
Points: 19, 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!
Post #888378
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse