friend system table

[Jaynesh]

Hello

 

When you have a a friend system and you have a table for friends such as

 

friends(my_id, friend_id)

 

Is it ideal to have a duplicate entry for each friend relationship, such as:

 

my_id

1

2

friend_id

2

1

[gizmola]

Yes if you're using innodb which has a clustered index.  If not, it's more of a tossup -- double the data, vs half the data but 2 indexes.  With that said, the cost on the size of the indexes is something you only have to pay on insert. 

[Jaynesh]

Ok so if I had one entry how would I adjust my query to affect it both ways.

 

Currently my query pulls a list of all the users friends. This is assuming that there is a double entry.

 

If I have a single entry for e.g

 

user_id  friend id

1            2

 

It treats 2 as a friend of 1 but does not treat 1 as a friend of 2.

 

"SELECT * FROM Users, Friends WHERE user_id = $user and Friends.friend_id = Users.id"

[gizmola]

SELECT * FROM Users, Friends WHERE (Friends.user_id = $user AND Users.id = Friends.friend_id) OR (Friends.friend_id = $user AND Users.id = Friend.user_id)

 

You should probably create a load of test data and benchmark both approaches.  Also make sure you explain extended on the queries.

 

 

[Jaynesh]

Thank you!!