如何使用MySQL和PHP在数据库中的用户之间存储相关值？

问题描述：

I'm trying to make a database of users for a website that will store correlation values between all the users. What I mean by this is that for every pair of users, there is a stored value of correlation between the two users.

The correlation values will be calculated by PHP using a correlation algorithm. My question is what is the most correct way to store them in a MySQL database? I realize I could make a table like this:

         ---------------------------------
        | user1 | user2 | user3 | etc... |
 -----------------------------------------
| user1 | #val  | #val  | #val  | #val   |
 -----------------------------------------
| user2 | #val  | #val  | #val  | #val   |
 -----------------------------------------
| user3 | #val  | #val  | #val  | #val   |

etcetera. But I don't like this method because

It stores every value twice; for example the correlation between user1 and user3 is stored in row 1 column 3 as well as row 3 column 1.
I use prepared statements, which means I can't select columns named after user IDs unless I concatenate the user ID into the SQL statement, which is obviously not ideal.

What are my alternatives? If this can be done in MySQL well, how do I go about it?

If this can't be done well in MySQL, are there any other database types I should try to learn? For example, I realize a graph database system may work well for this, but I don't want to spend time learning how to use a graph database if this can be done in MySQL.

我正在尝试建立一个网站用户数据库，用于存储所有用户之间的相关值。我的意思是，对于每对用户，两个用户之间存在相关的存储值。 p>

相关值将由PHP使用相关算法计算。我的问题是将它们存储在MySQL数据库中的最正确方法是什么？我意识到我可以制作一个这样的表： p>

  --------------------------  ------- 
 |  user1 |  user2 |  user3 | 等... | 
 ----------------------------------------- 
  |  user1 |  #val |  #val |  #val |  #val | 
 ----------------------------------------- 
 |  user2 |  #val |  #val |  #val |  #val | 
 ----------------------------------------- 
 |  user3 |  #val |  #val |  #val |  #val | 
  code>  pre> 
 
 等。 但是我不喜欢这种方法，因为 p> 
 
 

它存储了每个值两次; 例如，user1和user3之间的关联存储在第1行第3列以及第3行第1列。 li> 
 
我使用预准备语句，这意味着我无法选择以用户ID命名的列，除非 我将用户ID连接到SQL语句中，这显然不太理想。 li> 
  ul> 
 
 我的替代方案是什么？ 如果这可以在MySQL中完成，我该如何解决呢？ p> 
 
 
如果在MySQL中无法做到这一点，我是否应该尝试学习其他数据库类型？ 例如，我意识到图形数据库系统可以很好地工作，但我不想花时间学习如何使用图形数据库，如果这可以在MySQL中完成。 p> 
  div>

答

Typically you would do something like this in a JOIN table. So say you have a users table that has a user_id field and whatever other fields you need. You could build a table called user_relations or similar that would simply have two user_id foreign key fields that would relate users in some manner.

user_id_1  |   user_id_2
------------------------
1          |   2
1          |   3
2          |   1
3          |   1
...        |   ...

You would then have a compound primary key across both columns to enforce uniqueness. Note that I am assuming that the #val you mentioned in your question was simply some kind of flag (1/0) that represents the existence of a relationship. If you actually need that value to explain something about the relationship (i.e. parent/child or some other meaningful value) then you can obvioously add a third column to this table to store the value associated with the relationship.

When you need to query across a relation you could do it like this:

SELECT u1.*, u2.*
FROM
  users AS u1
  INNER JOIN user_relations AS ur
    ON u1.user_id = ur.user_id_1
  INNER JOIN users AS u2
    ON ur.user_id_2 = u2.user_id
WHERE u1.user_id = ? /* or whatever filter you may need to apply */

Note that depending on what type of relationship you are trying to represent (i.e. a two-way relationship) you may need two rows in the table to represents each relationship. That way you can always use the first column to look up all related users in the second column. This is shown in my example above where the relationships of user one are shown in both directions in the sample rows values.

答

In my opinion the best solution is to have 2 tables... Users and UsersRelations

UserRelations:

====================================
User1Field | User2Field | ValueField
====================================
#User      | #User      | #val
------------------------------------
#User      | #User      | #val
------------------------------------
#User      | #User      | #val
------------------------------------

答

Brilliant question.

Given users A, B, C, D and E, your data set is triangular;

  A B C D E
A   
B *  
C * *
D * * *
E * * * *

In the above matrix AA, BB, CC, DD and EE are meaningless.
To avoid duplication, AB is the same as BA. CD is the same as DC, and so on.

You could save a triangular data-set in a table-orientated SQL database in this way;

id usr usr c
------------
0  A   B   1
1  A   C   5
2  A   D   3
3  A   E   4
4  B   C   3

etc

如何使用MySQL和PHP在数据库中的用户之间存储相关值？

相关推荐