Many to many relationship with attributes and surrogate key

0
votes

I have the following tables:

users

  • id INT PK
  • created_at DATETIME
  • updated_at DATETIME
  • is_archived BOOLEAN
  • name VARCHAR

publishers

  • id INT PK
  • created_at DATETIME
  • updated_at DATETIME
  • is_archived BOOLEAN
  • name VARCHAR

subscriptions

  • user_id INT PK,FK
  • publisher_id INT PK,FK

I want to have additional attributes in my the subscriptions such as created_at, updated_at and is_archived (like all other tables) and other fields.

My options are:

  • user_id INT PK,FK
  • publisher_id INT PK,FK
  • created_at DATETIME
  • updated_at DATETIME
  • is_archived BOOLEAN
  • expires_at DATETIME

OR

  • id INT PK
  • user_id INT FK
  • publisher_id INT FK
  • created_at DATETIME
  • updated_at DATETIME
  • is_archived BOOLEAN
  • expires_at DATETIME

What are the pros/cons of having an autoincrement key instead of using both foreign keys as a primary composite key?

2
mysqlsql

2 Answers

2
votes

In mapping tables for many-to-many relationships I typically use a compound primary key of the two items. The main pro of this approach is that the uniqueness of the record as a combination of the two is enforced by them being the primary key. Also, the existence of a separate auto-increment id in most cases doesn't provide much benefit or give a row any more meaning so to me it is noise more than anything else. That said, it can be beneficial if the mapping of the two tables is an important domain entity in its own right that will be used on occasion without joining the other tables to it.

The main con of this approach is that queries can be a bit more verbose and you generally cannot directly access a record without having a join against the other tables in the relationship. It also enforces uniqueness, so you can't have multiple instances of the same user and publisher together.

If subscriptions are an important entity in your application that can standalone without a user or publisher then you may want to go with the auto-increment. Additionally, if you have the need to have multiple records for any combination of user and publisher (which seems like you will) then the auto-increment approach is the correct way to go.

2
votes

For me this depends mainly on how you are going to access the data.

If you always start from a users or publishers point of view and just want to get the related data, an additional primary key will be pretty much obsolete.

If the subscriptions table in itself has a lot of data and is not always depending on the user or publisher data to make sense, then an additional primary key would make sense to access the data.

So if it is a conventional linking table I don't see a reason to add a seperate primary key. If you can look at it more like a seperate entity - usually if you can give it a more meaningful and independent name - which has 1:m relationships to users and publishers then an additional primary key makes sense.