好友列表：关系数据库表设计 [英] Buddy List: Relational Database Table Design

问题描述

所以，现代概念的好友列表：_
假设我们有一个名为Person的表。现在，那个人需要有很多的朋友（其中每个哥们也在同一个班级）。构建关系的最明显的方式是通过连接表。
ie

  buddyID person1_id person2_id 
 0 1 2 
 1 3 6 
但是，当用户想要看到他们的好友列表时，该程序将不得不检查列'person1_id'和''' person2_id'以查找他们所有的好友。
 
 
 
这是实现这种表格的恰当方法，还是更好地将记录添加两次.. ie 
  buddyID person1_id person2_id 
 0 1 2 
 1 2 1 
  
只需搜索一列。
 
 
 
提前感谢。 p> 
 
解决方案
这是一个很多关系，这是一个需要的一个relator表。
 创建表Person（
 person_id int not null主键，
用户名varchar（100）not null，
 ... other_cols ... 
 
 
 
创建表Buddy（
 person_id1 int not null，
 person_id2 int not null，
主键（person_id1，person_id2） ，
 foreign key（person_id1）reference Person（person_id），
 foreign key（person_id2）reference Person（person_id）
）
  
所以Person表会显示每个Person包含1行。它会包含任何关于伙伴的数据，因为这样会使其非规范化。相反，Buddy表将包含Person之间的关系。
 
 
 
所以让我们说在Person表中有这样的一个：
  person_id用户名
 1 George 
 2 Henry 
 3 Jody 
 4 Cara 
  
亨利和卡拉是好友，乔治和卡拉也是如此：
  person_id1 person_id2 
 2 4 
 1 4 
  
如果你需要拥有它，使得关系不是隐含的相互的，那么你将需要添加额外的行来使之变得明确。所以现在让我们说亨利认为卡拉是个好兄弟，卡拉也认为亨利是一个好伙伴，而乔治认为卡拉是个好兄弟，但是卡拉并不和乔治一起回报：
  person_id1 person_id2 
 2 4 
 4 2 
 1 4 
  
失踪的4 1表示Cara不认为George是一个好友。这使事情变得很干净，避免数据异常。您可以调整关系，而不会与Person数据混淆。此外，您可以在外键上定义删除级联规则，以便删除Person会自动删除所有关联的关系。相反，您可能希望防止这种情况，而在这种情况下，您可以在外键上指定限制（默认），以防止删除关系仍然定义的人。
 
 
 
查询也很简单：
 
 
 
 Cara有多少个好友（让我们假设好友列表的关系是隐含的）：
  select Person（*）from Person 
 join buddy on person_id = person_id1或person_id = person_id2 
其中name ='Cara'
  
对于没有隐含关系的情况，可能会更好地重命名列：
  person_id consider_as_buddy_id 
 2 4 
 4 2 
 1 4 
 4 3 
 
从Person P 
中选择count（*）加入Buddy B on P.person_id = B.person_id 
其中name ='Cara'
  
这会返回Cara认为是好友的人数。在这种情况下2.虽然Jody不认为Cara是一个好友 - 所以要找出相互关系，你会这样做：
 从Person P 
中选择count（*）在Pperson_id = B.person_id和
上连接好友B B.considers_as_buddy_id = P.person_id 
其中name ='Cara'
  
 
So, the modern concept of the buddy list:

Let's say we have a table called Person. Now, that Person needs to have many buddies (of which each buddy is also in the person class). The most obvious way to construct a relationship would be through a join table.
i.e.
buddyID   person1_id   person2_id
0         1            2
1         3            6
But, when a user wants to see their buddy list, the program would have to check the column 'person1_id' and 'person2_id' to find all of their buddies.

Is this the appropriate way to implement this kind of table, or would it be better to add the record twice.. i.e.
buddyID   person1_id   person2_id
0         1            2
1         2            1
So that only one column has to be searched.

Thanks in advance.
解决方案
This is a many to many relationship, this a relator table is required.
create table Person (
   person_id int not null primary key,
   username varchar(100) not null,
   ... other_cols ...
)


create table Buddy (
   person_id1 int not null,
   person_id2 int not null,
   primary key (person_id1, person_id2),
   foreign key (person_id1) reference Person (person_id),
   foreign key (person_id2) reference Person (person_id)
)
So the Person table would contain 1 row for each Person obviously.  It would contain any data whatsoever about buddies as that would make it denormalized.  Instead the Buddy table would contain the relationships between the Persons.

So lets say you have something like this in the Person table:
person_id    username
1            George
2            Henry
3            Jody
4            Cara
Henry and Cara are buddies, as are George and Cara so:
person_id1   person_id2
2            4
1            4
If you need to have it such that the relationships are not implicitly mutual then you will need to add additional rows to make that be explicit.  So now lets say that Henry considers Cara a buddy and Cara likewise thinks of Henry as a buddy while George considers Cara as a buddy but Cara does not reciprocate with George:
person_id1   person_id2
2            4
4            2
1            4
The missing 4  1 indicates that Cara does not consider George as a buddy.  This keeps things very clean and avoids data anomolies.  You can adjust the relationships without mucking with the Person data.  Also you could define a delete cascades rule on the foreign keys so that deleting Person would automatically delete all associated relationships for you.  Conversely you may want to prevent that instead in which case you could specify restrict (the default) on the foreign keys that would prevent deletion of a Person with relationships still defined.

Queries are easy too:

How many buddies does Cara have (lets assume relationships for buddy lists are implicit):
select count(*) from Person 
                join Buddy on person_id = person_id1 or person_id = person_id2
 where name = 'Cara'
For the case where the relationships are not implied it might be better to instead rename the columns like so:
person_id   considers_as_buddy_id
2           4
4           2
1           4
4           3

select count(*) from Person P
                join Buddy B on P.person_id = B.person_id
 where name = 'Cara'
This returns how many people Cara considers as buddies.  In this case 2. While Jody does not think of Cara as a Buddy - so to find out the mutual relationships you would do this:
select count(*) from Person P
                join Buddy B on P.person_id = B.person_id and 
                                B.considers_as_buddy_id = P.person_id
 where name = 'Cara'


                        
这篇关于好友列表：关系数据库表设计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！