One or Two Primary Keys in Many-to-Many Table?

Question

I have the following tables in my database that have a many-to-many relationship, which is expressed by a connecting table that has foreign keys to the primary keys of each of the main tables:

• Widget: WidgetID (PK), Title, Price
• User: UserID (PK), FirstName, LastName

Assume that each User-Widget combination is unique. I can see two options for how to structure the connecting table that defines the data relationship:

1. UserWidgets1: UserWidgetID (PK), WidgetID (FK), UserID (FK)
2. UserWidgets2: WidgetID (PK, FK), UserID (PK, FK)

Option 1 has a single column for the Primary Key. However, this seems unnecessary since the only data being stored in the table is the relationship between the two primary tables, and this relationship itself can form a unique key. Thus leading to option 2, which has a two-column primary key, but loses the one-column unique identifier that option 1 has. I could also optionally add a two-column unique index (WidgetID, UserID) to the first table.

Is there any real difference between the two performance-wise, or any reason to prefer one approach over the other for structuring the UserWidgets many-to-many table?

Answer 1

You only have one primary key in either case. The second one is what's called a compound key. There's no good reason for introducing a new column. In practise, you will have to keep a unique index on all candidate keys. Adding a new column buys you nothing but maintenance overhead.

Go with option 2.

Answer 2

Personally, I would have the synthetic/surrogate key column in many-to-many tables for the following reasons:

• If you've used numeric synthetic keys in your entity tables then having the same on the relationship tables maintains consistency in design and naming convention.
• It may be the case in the future that the many-to-many table itself becomes a parent entity to a subordinate entity that needs a unique reference to an individual row.
• It's not really going to use that much additional disk space.

The synthetic key is not a replacement to the natural/compound key nor becomes the PRIMARY KEY for that table just because it's the first column in the table, so I partially agree with the Josh Berkus article. However, I don't agree that natural keys are always good candidates for PRIMARY KEY's and certainly should not be used if they are to be used as foreign keys in other tables.