Q:

Use composite keys? Or always use surrogate keys?

Duplicate: Many to Many Relation Design - Intersection Table Design

If I have these tables (* = primary key):
user
  id*
  name

group
  id*
  name

Is this better?
user_group
  user_id*
  group_id*

Or is this better?
user_group
  id*
  user_id
  group_id

A:

I am always voting for keys that are as narrow as possible, static (never change) and thus I usually favor a surrogate INT as a key over a compound key.
If you want to reference a compound key from another table, you'll always have to specify several conditions - which can get quite unwieldy at times!
Also check out some of those links:

http://www.agiledata.org/essays/keys.html
http://rapidapplicationdevelopment.blogspot.com/2007/08/in-case-youre-new-to-series-ive.html

(of course, others will likely post at least 10 links PRO natural keys :-) )
There's no harm in using a surrogate key - go for it! :-)
Marc

A:

Given than both user_id and group_id are already surrogate keys and thus guaranteed to be unique (given proper implementation of the app), adding a third id is totally redundant.
Unless you are using some kind of ORM which (sadly) usually make more complex the handling of composite keys. In that case you must evaluate the cost of redundancy versus the ease of development with your chosen ORM.

A:

Whenever I've omitted a surrogate primary key from a join table, I've come to regret it.  It just seems like when I get around to writing the code to manage the relation that having a surrogate primary key is very handy.  I typically follow an Active Record pattern and am using ASP.NET MVC, though, so your mileage may vary.  In the MVC world, having a single id key you can put on the end of the URL is very advantageous.

