Design help

I have two tables Publications and TM_Pub.

CREATE TABLE [dbo].[Publications] (
[Pub_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_Pres] [int] NULL ,
[Title] [nchar] (200) COLLATE Latin1_General_CI_AI NULL ,
[Authors] [char] (100) COLLATE Latin1_General_CI_AI NULL ,
[Location] [char] (50) COLLATE Latin1_General_CI_AI NULL ,
[Publication] [char] (150) COLLATE Latin1_General_CI_AI NULL ,
[Pub_date] [datetime] NULL ,
[Pres_enddate] [datetime] NULL ,
[Pres_Desc] [text] COLLATE Latin1_General_CI_AI NULL ,
[Pub_detail] [char] (20) COLLATE Latin1_General_CI_AI NULL ,
[keywords] [text] COLLATE Latin1_General_CI_AI NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE TABLE [dbo].[TM_PUB] (
[TM_PUB_ID] [int] IDENTITY (1, 1) NOT NULL ,
[Pub_ID] [int] NULL ,
[TM_ID] [int] NULL
) ON [PRIMARY]

Each Pub_id can have multiple TM_id's. But I want to ensure that I do not create any duplicate Pub_id; TM_id records.

The user will be looking at an indivdual Publication record (Pub_id) along with a all the available TM_id's, with the TM_id's currently assigned to the Pub_id selected. The user will be able to select or unselect TM_ids for the Pub_id.

I am having trouble figuring out how to take the users selection and insert new records in the TM_Pub table if they do not already exist as well as delete records if the do exist. I plan to send my sproc two parameters @.pub_id and @.TMidstring.

I am looking for ideas on how best to accomplish this.

Thanks.Hi

If I've understood your problem, why don't you just use Pub_ID and TM_ID as a composite key? Like that you won't be able to have any duplicates.

In the case of a junction table like this, I would delete all the old records for a particular Pub_ID or TM_ID, and insert all the new ones, so that you don't have to check each time if they already exist.

Does that help or am I way off?!

A.