Alex Rivera | Logout

SQL: Do you need an auto-incremental primary key for Many-Many tables?

Asked 2009-04-26T06:13:42.003
17

Say you have a Many-Many table between Artists and Fans. When it comes to designing the table, do you design the table like such:

ArtistFans
    ArtistFanID (PK)
    ArtistID (FK)
    UserID (FK)

 (ArtistID and UserID will then be contrained with a Unique Constraint 
  to prevent duplicate data)

Or do you build use a compound PK for the two relevant fields: 

ArtistFans
    ArtistID (PK)
    UserID (PK)

(The need for the separate unique constraint is removed because of the 
 compound PK)

Are there are any advantages (maybe indexing?) for using the former schema?

sql database-design
Edit
Report

2 Answers

2

I cannot think of any reason to use the first form you list. The compound primary key is fine, and having a separate, artificial primary key (along with the unique contraint you need on the foreign keys) will just take more time to compute and space to store.

answered 2009-04-26T06:24:22.337
1

The standard way is to use the composite primary key. Adding in a separate autoincrement key is just creating a substitute that is already there using what you have. Proper database normalization patterns would look down on using the autoincrement.

answered 2009-04-26T06:25:35.110

Your Answer