朋友桌最佳方法
+-----------------------------------+
| fID | personA | personB | fStatus |
|-----------------------------------|
| 1 | u1ID | u2ID | 1 |
| 2 | u3ID | u4ID | 0 |
| 3 | u1ID | u3ID | 1 |
+-----------------------------------+
Status Column Codes:
0: Pending (personA wants to be friends with personB)
1: Friends (personA with personB)
* On removal request by either side > delete the corresponding row
我想要一个简单的 MySQL 表，通过它我可以在我的网站上实现友谊选项。是否是朋友，就是它所需要拥有的一切。
现在我想知道实现此目的的最佳方法，以及是否有我遗漏的东西。我想查询每个用户的朋友，并且用户应该能够在他们的朋友列表中添加或删除彼此。
+-----------------------------------+
| fID | personA | personB | fStatus |
|-----------------------------------|
| 1 | u1ID | u2ID | 1 |
| 2 | u3ID | u4ID | 0 |
| 3 | u1ID | u3ID | 1 |
+-----------------------------------+
Status Column Codes:
0: Pending (personA wants to be friends with personB)
1: Friends (personA with personB)
* On removal request by either side > delete the corresponding row
I want to have a simple MySQL table by which I could implement a friendship option on my website. Being friends or not is all it needs to possess.
Now I want to know about the best approach for this purpose, and if there is something that I'm missing. I want to query friends of each user and users should be able to add or remove each other from their friend-list(s).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
由于每个人都可以与另一个人成为朋友，因此您需要实现 NxM（多对多）关系。这在关系数据库管理系统中翻译为另一个表中的“连接”，就像您所做的那样。
http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx
在你的例子中，也许您可能想添加一列来了解谁向其他人请求了友谊。
编辑：我可能会删除 fId 列并创建列（外键） personA 和 personB 的组合主键。这样数据库就可以避免对同一个人有多个请求，也可以避免出现这样的情况：personA 向 personB 和 personB 请求友谊，而不是仅仅接受它，而是向 personA 请求友谊，从而导致两条记录！
编辑2：您可以使用状态字段来存储友谊请求“所有者”：
编辑3：您不需要到期时间（列request_date）吗？如果好友请求在 3 个月内没有得到答复，那么该请求就会过期并被删除。
Since each person could be friend with another person, you need to implement a NxM (many to many) relation. That translates, in a relational database managemen systemt, in another table, to make the "junction", just as you did.
http://weblogs.asp.net/zeeshanhirani/archive/2008/08/21/many-to-many-mappings-in-entity-framework.aspx
In you example, maybe you may want to add a column to know who asked the friendship to who else.
edit: I would maybe remove the fId column and create a composed primary key of the columns (foreign keys) personA and personB. This way it will be avoided by the db to have more than one request for the same persons, and also to avoid a situation where personA ask friendship to personB and personB, instead of just accepting it, asks friendship to personA, resulting in two records!
edit2: you could use the status field to store the friendship request "owner":
edit3: don't you need an expiration time (column request_date)? if a friendship request is not answered in, say, 3 months, then the request expires and it is deleted.