databasedata-structuresindexingschema

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

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:

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?

Solution

  • 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.