locked
Inserting data into bridge table (associate table) RRS feed

  • Question

  • Hi,

    I have a bridge (associate) table with with 3 columns like follows.
         
           1. Column 1- identity column (to enforceprimary key)
           2. Column 2 - Reference from Table A ( A is a fact table)
           3. Column 3 - Reference from Table B ( B is a fact table as well)

    What is the best way to insert record into this bridge table. Kindly help me resolve this ASAP.

    Best regards,
    Sengwa



    Tuesday, July 7, 2009 6:49 PM

Answers

  • Then how you insert them into your bridge table depends on how your source stores them.

    You'll have to:
    Read your source table(s) that link crashes to causes.
    Normalize (if necessary) those rows so that you get one crash to cause link in each row.
    Perform a surrogate key lookup into the cause dimension.
    You may need to perform a surrogate key lookup into the crash fact table - I don't know how you uniquely identify each row in the fact table.  If you don't uniquely identify each row, but have a "degenerate dimension" which is the crash_id, then you need to do nothing.
    Insert those rows into your bridge table.
    Todd McDermid's Blog
    • Marked as answer by Sengwa Wednesday, July 8, 2009 3:35 PM
    Tuesday, July 7, 2009 11:08 PM

All replies

  • How do you determine what relationships should be in this table?

    Is it a many-to-many, one-to-one, many-to-one, or one-to-many relationship?
    Todd McDermid's Blog
    Tuesday, July 7, 2009 7:55 PM
  • its many to many relationship. column 2 and 3 are foreign key from Table A and Table B.

    Best regards,
    Sengwa

    Tuesday, July 7, 2009 8:01 PM
  • You need to provide more details about your scenario. Do you have a source table(s) to derive the content of the bridge table?
    Rafael Salas | Don’t forget to mark the post(s) that answered your question http://rafael-salas.blogspot.com/
    Tuesday, July 7, 2009 8:03 PM
  • Yes. I have source to derive the content of the bridge table. Scenario is,

    a. I have a crash fact table and contributing cause dimension table table.
    b. one crash record may have multiple contributing causes records.

    To handle this, i have designed the bridge table with Crash_id and ContributingCause_id. the records has to inserted like,

    crash_id1, cause_id1
    crash_id1, cause_id2
    crash_id1, cause_id3
    crash_id1, cause_id4
    crash_id1, cause_id4

    crash_id2, cause_id1
    crash_id2, cause_id2
    crash_id2, cause_id3

    crash_id3, cause_id1
    crash_id3, cause_id2

    Guess this would have explained my scenario.

    Best regards,
    Sengwa



    Tuesday, July 7, 2009 10:45 PM
  • Then how you insert them into your bridge table depends on how your source stores them.

    You'll have to:
    Read your source table(s) that link crashes to causes.
    Normalize (if necessary) those rows so that you get one crash to cause link in each row.
    Perform a surrogate key lookup into the cause dimension.
    You may need to perform a surrogate key lookup into the crash fact table - I don't know how you uniquely identify each row in the fact table.  If you don't uniquely identify each row, but have a "degenerate dimension" which is the crash_id, then you need to do nothing.
    Insert those rows into your bridge table.
    Todd McDermid's Blog
    • Marked as answer by Sengwa Wednesday, July 8, 2009 3:35 PM
    Tuesday, July 7, 2009 11:08 PM