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, Helenayou'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|||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|||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|||Ok, I see your point..

Eliminating the duplicates is unfortunately not an option.

Anyway, thanks for your help!sql

How i could connecting to Analysis Services from BI Intelligence Studio with server authenticati

Hi:

I have been working at client work station with BI Intelligence Studio but i want deploy the project in server machine, but my windows credential don't have sufficient privilege for do it. However i know the server credential for do it but i can't find the project settings for do that.

The account you connect to the AS server as will need permisions to deploy the database. In the Windows Start menu, you can right-click on the BI Studio item and select Run As... and this will give you the option to run the UI under a different windows use account.

This account does not necessarily need permissions to access the source relational data as you can specify in a data source to use the service account or impersonate a specific account. Additionally, many data sources will let you save a user name and password in the connection string. (For security reasons, Analysis Services will remove the password form the connection string, but will prompt you for it later as necessary and will store encrypted under the current user credentials to avoid excessive prompting.)