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

Question

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?

Answer 1

ArtistFans
    ArtistID (PK)
    UserID (PK)

The use of an auto incremental PK has no advantages here, even if the parent tables have them.

I'd also create a "reverse PK" index automatically on (UserID, ArtistID) too: you will need it because you'll query the table by both columns.

Autonumber/ID columns have their place. You'd choose them to improve certain things after the normalisation process based on the physical platform. But not for link tables: if your braindead ORM insists, then change ORMs...

Edit, Oct 2012

It's important to note that you'd still need unique (UserID, ArtistID) and (ArtistID, UserID) indexes. Adding an auto increments just uses more space (in memory, not just on disk) that shouldn't be used