处理PHP MySQL中多对多关系的最佳方法 [英] Best way to handle Many-to-Many relationships in PHP MySQL

问题描述

我正在寻找处理PHP和MySQL中多对多关系数据库的最佳方法.

I am looking for the best way to handle a database of many-to-many relationships in PHP and MySQL.

现在我有2张桌子:

用户(ID，用户名，名字，姓氏)
连接(id_1，id_2)

Users (id, user_name, first_name, last_name)
Connections (id_1, id_2)

在User表中，id在添加时自动递增，并且user_name是唯一的，但是可以更改.不幸的是，我无法控制user_name及其更改的能力，但我必须对此加以考虑.

In the User table id is auto incremented on add and user_name is unique, but can be changed. Unfortunately, I don't have control over the user_name and its ability to be changed, but I must account for it.

Connections表显然是用户1和用户2的ID.

The Connections table is obviously, user1 and user2's id.

连接表需要考虑以下可能的关系:

The connection table needs to account for these possible relations:

user1 --> user2 (user 1 friends with user 2 but not user2 friends with user1) 
user2 --> user1 (user 2 friends with user 1 but not user1 friends with user2) 
user1 <--> user2 (user 1 and user 2 mutually friends) 
user1 <-!-> user2 (user 1 and user 2 not friends) 

那不是问题，我要解决的问题是，当这些关系批量更改时，保持它们的唯一性.

That part is not the problem, The problem I am having with is keeping these relations unique when and if they change in batches.

可能的解决方案1:删除所有用户1的关系，并使用更新的列表读取它们.我认为这可能太慢了，无法满足我的需求.

Possible solution 1: delete all of user 1's relations and readd them with the updated list. I think this might be too slow for my needs.

解决方案2?还有其他人遇到这个问题吗?我该如何最好地处理呢?

Solution 2? Anyone else encounter this problem? How should I best handle this?

更新:区分关系:

我处理这样的关系:

user1, user2
user1, user3
user2, user1

在该示例中，以下内容为真:
user1跟随user2和user3
user2仅跟随user1，但不跟随user3
user3不跟随user1或user2

in that example the following is true:
user1 follows user2 and user3
user2 only follows user1 but doesn't follow user3
user3 doesn't follow either user1 or user2

推荐答案

如果您在唯一性方面遇到问题，则可以在Connections上使用复合主键，同时使用两列(id_1，id_2).

You could use a compound primary key on Connections, using both columns (id_1, id_2) if you're having problems with uniqueness.

这篇关于处理PHP MySQL中多对多关系的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！