Use composite keys? Or always use surrogate keys?

Question

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

Answer 1

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