primary key in many-to-many join-table 1

More than likely the second as this is clearly a joining table. The extra id is just unneed overhead. You still will need the unique index onthe two id fields or you will have a data problem, so why not make it the primary key. You have to store one or the other of those fields in the related tables anyway. There really isn't any reason why you would ever use the UserGroupID anywhere else.

Questions about posting. See faq183-874

Right now the table's function is to create a relationship between two separate entities. If that's it's only purpose, then I'm ok with the composite PK. If I decide in the future that this relationship has other properties, such as the date the relationship was made, the records in this table become more than a simple join but actual business entities themselves. Does not having a primary key that is unrelated to the data then cripple the system?

Does not having a primary key that is unrelated to the data then cripple the system?"

no, it won't cripple the system

and i would argue that the composite pk is very related to the data in that table, even more so when there actually is other data in the table besides the composite key

even with history (multiple rows for the same userid, groupid key pair), the pk will just expand to encompass enough columns to make it unique -- e.g. userid, groupid, effectivedate

i can't think of any scenario where a surrogate key is a good idea here

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

As I see it, the only advantage of the unique single column (surrogate) key is if a userID and groupID could be associated more than once across another dimension, such as time as dragonwell has suggested.

Example (for clarity) - UserID is associated with a person's health coverage. GroupID is associated with coverage with a particular group, say United Auto Workers union. Member leaves factory for non-union job for a period of time, then returns to factory working for union. If member retains same UserID, then a potential need for tracking the duplication occurs.

Also to be considered (but not here) are potential performance degradation issues (no specific database was named) associated with concatenated primary keys where the total length of thekey exceeds 32 bytes, or where the columns being concatenated have different data types.

-------------------------
The trouble with doing something right the first time is that noboby appreciates how difficult it was.
- Steven Wright