我是否应该对映射表使用复合键，该复合键也用于外键？
我正在使用 ASP.NET 和实体框架来制作一个网站。 我目前有一个映射表，用于表示用户和足球队之间的多对多关系。 所以：
用户
团队
UserTeams
第 1 部分：使用复合键作为映射表的主键是否是最佳实践？ 换句话说：
UserTeams 表
PK 用户 ID
PK 团队ID
PreferenceId
第 2 部分：需要注意的是我还有另一张表。 我们将其称为“UserTeamPredictions”，它存储用户对每年给定团队的预测。 该表有一个指向映射表的外键。 所以它看起来像这样：
UserTeamPredictions 表
PK UserTeamPredictionId
FK 用户 ID
FK 团队ID
预言 PredictionYear
这似乎在实体框架中工作正常，但是，在引用我使用的第三方控件（如 Telerik）中的关系时遇到了一些问题。 即使它可能不是理想的数据设置，我是否应该更改表结构/关系，以便更容易在代码中使用数据绑定和其他内容？
更改是向 UserTeams 映射表添加一个整数主键，允许 UserTeamPredictions 表直接引用该键，而不是像当前那样通过复合键：
UserTeams 表
PK UserTeamId
FK 用户 ID
FK 团队ID
PreferenceId
UserTeamPredictions 表
PK UserTeamPredictionId
FK UserTeamId
预言 预测年
你觉得怎么样！？
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!?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
你应该改变它。 搜索堆栈溢出以获取有关“自然键”的讨论 - 几乎普遍认为代理键更好，尤其是在使用实体生成时。 一般来说，自然键或复合键不能很好地与实体框架样式的 DAL 层配合使用。 例如，Lightspeed 和 Subsonic 都要求你有一个唯一的列作为 PK...Lightspeed 在其当前版本中甚至坚持将你的列称为“Id”，尽管这将在下一个版本中改变。
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.
我会选择不这样做。 我将使用代理键并在 UserId 和 TeamId 列上放置唯一索引。 当复合键超过两个时，我真的厌倦了复合键，而不是混合使用复合键和代理键，而是选择尽可能使用所有代理、无意义的自动增量键。
这样做的好处是可以为您提供良好的连接性能，并且意味着您始终知道给定表的键（表名 + ID），而无需引用架构。 一些 ORM 工具也只能在单列而不是复合键上正常工作。
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.