Buddy List: Relational Database Table Design

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 bu开发者_开发百科ddy 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.

---

There are a couple of possible scenarios here.

Firstly you need to determine if the buddy relationship can be one way. Can person A be a buddy to person B without B being a buddy of A? I would assume not, but it's worth being explicit.

Commonsense would suggest that you query more than you update relationships. As such, optimize your data for that. When creating a buddy relationship, create two recrods: A to B and B to A. That will make the query trivial. So yes, go with the two records as you have considered already.

---

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'