What do bridge tables contain?
-
I understand that one technique for many-to-many relationship implementation in data warehouses in using bridge table. I have difficulties understanding its nature and what exactly in contains - real measures, or just keys? Good articles and books are welcome.
Thanks for your help!
-
I typed "sql bridge table" into my favourite search engine and got a few promising results. Another term for it is "junction table" - you may get some more results if you search for that as well.
John
-
Usually these are just tables with two dimension columns for a many:many relationship. I suppose if you wanted to add some measure in here, like weight, you could.
-
Such a table certainly could contain measures. The concept of a "bridge table" (AKA join table or association table among other names) is actually pretty subjective and difficult to pin down. One possible definition is that it's a table with two or more foreign keys. If that's correct then lots of tables are "bridge" tables but most of the time we don't call them that.
-
To emphasize the lesson that John Mitchell is trying to teach, become independent and don't limit yourself to "just" the answers you might get on a forum. Try his searches and try searching on the very question you asked.
And don't limit your search to a single try. John used "SQL Bridge Table". Try "Bridge Table SQL" and see that the first answer that comes back has a good explanation even though it's for a different type of database. Also make sure you look at the suggested searches as you're typing where you might find such gems as a reference to W3Schools.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
A great reference to data warehouse design is the Kimball Group. They have an article on bridge tables and an example here:
https://www.kimballgroup.com/2012/02/design-tip-142-building-bridges/
They wrote the book on data warehousing and dimensional modeling, literally!
Their work is continued by the group DecisionWorks:
-
Good links, Chris. Just to be sure, though, you don't have to have a data warehouse to use "Bridge Tables".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
That is true, but since the OP explicitly asked about bridge tables in data warehouses, I figured I'd give them the best resources I know of instead of telling them to do a random Google search and have to weed through all the material themselves.
- Chris Harshman wrote:
https://www.kimballgroup.com/2012/02/design-tip-142-building-bridges/
I'm not impressed with the suggestion in that article. Rolling data up into XML and then unwrapping it just to generate a new surrogate key! It's a good thing not everyone lives on Planet Kimball.
- Chris Harshman wrote:
Jeff,
That is true, but since the OP explicitly asked about bridge tables in data warehouses, I figured I'd give them the best resources I know of instead of telling them to do a random Google search and have to weed through all the material themselves.
Yeah, sorry. Short posts don't always tell the rest of the story. The first sentence was directed at you and the time you took to post some data warehouse links. The second sentence was directed at the OP or anyone else that might be thinking that you have to have a data warehouse in order to be able to use "Bridge Tables".
I'll try to be a bit more explicit on such things to avoid such confusion.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply