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

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.

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.

I switched to using a surrogate and it's been working fine so far. Thanks. – JohnBubriski Jul 27 '09 at 15:34.

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.

Thanks for your input as well! – JohnBubriski Jul 27 '09 at 15:35.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.