I'm looking for some advice on performance tuning (if it needs to be done) with a child table.
The Child table has only two columns: SET_ID, PID. However, I don't want a unique ID generated in a separate column. Instead, I wish to just create a primary key based on both of these columns. The SET_ID is a foreign key from the SET table (in that table, SET_ID is the unique primary key). Each item (PID) in this child table is associated with a particular set. The PIDs can reoccur throughout the table, as well as the SET_ID. However, I do not wish to have duplicate row values.
Is it wise to do a double column primary key in this case? I will be performing many UPDATEs on the table through VB ( [databaseConnObj].Update([datatableInMemory]) ). I just want to make sure that I won't be experiencing any bad performance issues before proceeding with this design. If you can think of any better way to do this, I would appreciate the input!