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
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:
(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
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.
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.
I would ordinarily agree with Vinko, but if you're using an ORM, like Hibernate, it can be a happier relationship (between you and Hibernate) if you give everything a surrogate key.
I normally recommend "artificial keys" (what you're calling "surrogate") because, as they're essentially meaningless outside the DB, you can guarantee they will never have to change due to external circumstances (while all kinds of other data about an entity well might).
But your user_group table, a typical "two foreign keys and that's all" arrangement used to implement a many:many relationship, is a different case -- the two foreign keys in question are just as much under your control as the surrogate would be. The surrogate key for a non-entity, i.e. a row that only exists to represent a little bit of a relationship, is basically just deadweight -- I'd recommend losing it.
The first example is sufficient. Even if you were to add attributes to the many-to-many table construct (like is_main_grp, etc.), there is no real need for a surrogate in the link table, and you'll probably always want a unique constraint on user_id, group_id anyway.
Only if you were to hang another many-to-one relationship on the link relationship (like tags), THEN I would think about having a surrogate in the user_group table, and I would not do it until it was a requirement (since a surrogate is relatively easy to add):
user
id*
name
group
id*
name
user_group
id*
user_id
group_id
is_main_grp
user_group_tags
user_group_id*
tag_id*
tags
id*
tag_txt
please check complete analysis here