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!
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.
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