Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unhappy Unanswered: how organize a junction table?

    Hi
    I am importing XML documents with XML bulk load.
    The tables look like this (kind of) with a junction table C connecting tableA and tableB.

    tableA(nameID integer PRIMARY Key IDENTITY, name string)

    tableB(placeID integer PRIMARY Key IDENTITY , placename string)

    tableC(placeID integer FOREIGN Key, nameID integer FOREIGN key)

    NameID and placeID have the property identity, and the xsd is mapped so that they are inserted in tableC as well as their parent table when they are generated.

    My problem now is, how do I make sure that each placename or name only occur once with one ID? When trying to insert a placename that already exist in the database, I dont want to generate a new placeID for it. Instead, I would like to fetch the existing placeID and inserting it with the new nameID in tableC. It should be a quite common problem, since most many to many relationships would result in a junction table. As mentioned, I am loading the data with XML bulk load, I dont know if that would complicate matter..
    All help will be greatly appreciated!
    regards, Helena

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're right, it is a common problem

    when using a surrogate key, such as an identity column, remember that each table must actually have a "real" primary key (called a candidate key in data modelling terminology)

    all you have to do is declare a unique constraint on the real key, and the database will then prvent you from entering duplicates


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    Thanks for your reply.

    The Identity columns in tableA and tableB are also the "real" primary key. The "real" key in tableC would be a composite key made up by tableA and tableB's primary keys. Name in tableA and placename in tableB are declared unique. The composite key in tableC should also be unique, then the database will prevent duplicates values. But when trying to insert a new name but an old placename for example, how do I get the new nameID but the old placeID into tableC? It feels like I need to do a search to see if the placename exists, if no just insert data, if yes fetch placeID for that particular placename and insert. And this would be necessary whenever a junction table is involved.

    /Helena

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps you could do some research on the difference between surrogate and natural keys, but i assure you, even if you declare an identity column as a primary key, so that as far as the database is concerned it is the primary key, it isn't the "real" primary key, not from a modelling perspective, not in regard to duplicates

    yes, make the composite key in tableC its primary key

    as for what to do with potential duplicates, and how to find the original entry, so duplcates are not entered, and so that the tableC row links the correct parent rows, i'm sorry, i cannot help you, i've never written an XSD application

    perhaps you could eliminate the dupes from the data before you attempt to load it with XML


    rudy
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    Ok, I see your point..

    Eliminating the duplicates is unfortunately not an option.

    Anyway, thanks for your help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •