What do bridge tables contain?

  • elea.grig

    Ten Centuries

    Points: 1226

    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!

  • John Mitchell-245523

    SSC Guru

    Points: 148257

    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

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 715053

    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.

  • nova

    Valued Member

    Points: 57

    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.

  • Jeff Moden

    SSC Guru

    Points: 994237

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Chris Harshman

    SSC-Forever

    Points: 41700

    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!

    https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/books/data-warehouse-dw-toolkit/

    Their work is continued by the group DecisionWorks:

    https://decisionworks.com/

     

  • Jeff Moden

    SSC Guru

    Points: 994237

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • Chris Harshman

    SSC-Forever

    Points: 41700

    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.

  • nova

    Valued Member

    Points: 57

    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.

     

    • This reply was modified 1 week ago by  nova.
  • Jeff Moden

    SSC Guru

    Points: 994237

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply