Should I use a composite key for a map table, which is also used for a foreign key?

Question

I am using ASP.NET and the Entity Framework to make a website. I currently have a map table for a many to many relationship between... let's say users and soccer teams. So:

Users
Teams
UserTeams

Part 1: Is it best practice to use a composite key for the primary key of the map table? In other words:

UserTeams table
PK UserId
PK TeamId
PreferenceId

Part 2: The caveat is that I also have another table. Let's call it "UserTeamPredictions" that stores the user's predictions for a given team for each year. That table has a foreign key that points back to the map table. So it looks something like this:

UserTeamPredictions table
PK UserTeamPredictionId
FK UserId
FK TeamId
Prediction PredictionYear

This seems to work fine in the Entity Framework, however, I have had some problems when referencing relationships in third-party controls that I use like Telerik. Even though it might not be the ideal data setup, should I change the table structure/relationships so that its easier to work with in the code with data binding and other things?

The change would be to add an integer primary key to the UserTeams map table, allowing the UserTeamPredictions table to reference the key directly, instead of through the composite key as it currently does:

UserTeams table
PK UserTeamId
FK UserId
FK TeamId
PreferenceId

UserTeamPredictions table
PK UserTeamPredictionId
FK UserTeamId
Prediction PredictionYear

What do you think!?

Answer 1

I would choose not to. I would use a surrogate key and put a unique index on the UserId and TeamId columns. I get really sick of composite keys when there are more than two, and rather than have a mix of composite and surrogate keys, I choose to go with all surrogate, meaningless autoincrement keys wherever possible.

This has the bonus of giving you good performance on joins, and means you always know the key for a given table (table name + ID), without having to reference the schema. Some ORM tools only work properly with single column rather than composite keys, too.

Answer 2

You should change it. Search stack overflow for discussions on "natural keys" - it's almost universally agreed that surrogate keys are better, especially when using entity generation. Natural or composite keys do not play well with entity framework style DAL layers in general. For example, Lightspeed and Subsonic both require that you have a single unique column as a PK... Lightspeed in it's current version even goes so far to insist that your column is called "Id", although that will be changing next version.