March 23, 2010 at 9:32 am
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_EntitiesGee, 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.
March 23, 2010 at 9:33 am
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.
March 23, 2010 at 10:32 am
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.
March 23, 2010 at 11:17 am
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
😀
March 23, 2010 at 11:22 am
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! 😀
March 23, 2010 at 11:22 am
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"! 😉
March 23, 2010 at 11:29 am
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[/url]
Learn Extended Events
March 23, 2010 at 12:01 pm
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
March 23, 2010 at 12:13 pm
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
March 23, 2010 at 12:19 pm
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.
March 23, 2010 at 3:51 pm
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.
March 23, 2010 at 6:24 pm
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.
March 23, 2010 at 7:35 pm
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.
March 24, 2010 at 12:39 am
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.
March 24, 2010 at 10:15 am
.../...
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
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply