具有用户和交互的关系数据库（最佳关系设计）
在关系数据库中，实现多个用户彼此之间有多次交互的关系的最佳方法是什么？
方法 1： 我有一个 interactions 表，其中两个属性是 userID1、userID2 和交互的类型。 问题：每次用户想要访问其交互时，我都必须查看表交互中所有用户的所有交互，并具体查找与那个用户。我猜这是一个耗时且繁重的处理。
方法 2： 另一方面，对于 user 表，我可以创建一个链接到 interactions 的字段与该用户的交互表链接的表 - 因此每个用户都有一个他自己与其他用户的交互表。 问题：对我来说，搜索交互似乎更快，因为每次用户访问其通知时我只需要他的 ID。但是，我必须为每个用户创建一个表，这对我来说似乎不是很好的设计，并且可能会带来很多问题。
在这种情况下使用的最佳（最有效和最佳实践）设计模式是什么？
In a relational database, what would be the best way to implement a relationship where several users each have several interactions with each other?
Method 1: I have an interactions table where two of the attributes are userID1, userID2 and the type of the interaction. Problem: Each time a user wants to access its interactions I will have to look into ALL interactions from ALL users ion the table interactions and find, specifically, every instance of interaction with that user. I would guess this is time-consuming and heavy processing.
Method 2: On the other hand, for the user table I can create a field which links to an interactions table that links with a table of interactions from that user - so each user has an interactions table from himself to other users. Problem: To me, it seems faster to search for interactions because every time the user accesses its notifications I just need his ID. However, I would have to create a table for each user which doesn't seem good design to me and can come with many problems.
What is the best (most efficient and best practice) design pattern to use in this case?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
第三范式建议
表：Users、PK UserID
表：交互 PK InteractionID
表 UserInteractions PK UsrIntID、FK UserID、FK InteractionID
每个交互可以有 2...N 个用户
每个用户可以有 0..N 个交互。
获取用户（交互器），在 UserInteractions 中查找它们以获取其交互和关联用户的列表。查找用户（Interactees）以获取其他参与者的用户数据
3rd normal form would suggest
Table: Users, PK UserID
Table: Interactions PK InteractionID
Table UserInteractions PK UsrIntID, FK UserID, FK InteractionID
Each Interaction can have 2...N users
Each User can have 0..N Interactions.
Get the User (Interactor), look them up in the the UserInteractions to get a list of their interactions and the associated Users. Lookup up Users (Interactees) to get user data for other participants