• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Table fields for "friends" ... more of a MySQL Question

#1
[eluser]jonnyjon[/eluser]
Hi guys,
I have a friends table but I'm debated how to lay it out and use it.

Currently. this is how it looks:

=======================================================
| id | user_id | user_id_2 | is_approved | is_blocked |
=======================================================

So if user 1 and 2 are friends it would require 2 records for that friendship, one record to say user 1 is friends with 2 and another to say user 2 is friends with 1.

==============================
| 1 | 1 | 2 | true | false |
| 2 | 2 | 1 | true | false |
==============================

There must be a more efficient way to do this?

#2
[eluser]rweston[/eluser]
Why do you think you need 2?

If i'm thinking what you're thinking, all you'll need to find the 'friends' of one user is something along the lines of:

SELECT * FROM friends WHERE user_id = 1 UNION SELECT * FROM friends WHERE user_id_2 = 1;

Will that give you what you want?

#3
[eluser]webthink[/eluser]
Well it really depends on the needs of your application. You could do it with a single record.
if it's something like facebook user_id_1 is the id of the user requesting freindship, user_id_2 is the recipient. is_approved = 1 if the recipient accepts the friendship. Is blocked I would have a seperate table for as people can probably block one another without being friends.
so if I'm coming up with a list of friends for user 88 you search where (user_id = 88 OR user_id_2 = 88) AND is_approved = 1;

#4
[eluser]webthink[/eluser]
Hmmm wonder if the UNION would be faster than the OR. My guess would be that the OR would be faster if you index user_1 and user_2 worth doing some benchmarking though Smile

#5
[eluser]rweston[/eluser]
[quote author="webthink" date="1204723954"]Hmmm wonder if the UNION would be faster than the OR. My guess would be that the OR would be faster if you index user_1 and user_2 worth doing some benchmarking though Smile[/quote]

mySQL unions perform very poorly [based on my limited experience, and the fact that i may have been doing something wrong] so i'd have to go with the OR solution.

#6
[eluser]Josh Giese[/eluser]
I have a social network with friendships. One table, using the union method works great for me.

#7
[eluser]rweston[/eluser]
[quote author="Josh Giese" date="1204726028"]I have a social network with friendships. One table, using the union method works great for me.[/quote]

I should have been clear. For this application, the Union VS OR probably doesnt make a big difference, but i tried SELECT A,B,C FROM Table1 WHERE A Like 'XY%';

where Table1 was a View which was a union of 2 base tables and of course, what was expected was that the underlying indices were to have been used, but that did not seem to be the case.

#8
[eluser]webthink[/eluser]
It seems we're losing sight of the issue a bit but rweston, I seem to remember something to do with LIKE statements not being able to use indicies. You might want to look into that.
Either way I think for jonnyjon's case the OR solution I provided above is probably the best way to go. That is after all what OR was designed for. UNIONS are typically used to join 2 tables of unrelated data which isn't really what we're dealing with here.

#9
[eluser]jonnyjon[/eluser]
Thanks for all the great replies guys. Good to hear I can use only 1 record per association.

#10
[eluser]rweston[/eluser]
[quote author="webthink" date="1204729489"]It seems we're losing sight of the issue a bit but rweston, I seem to remember something to do with LIKE statements not being able to use indicies. You might want to look into that.
Either way I think for jonnyjon's case the OR solution I provided above is probably the best way to go. That is after all what OR was designed for. UNIONS are typically used to join 2 tables of unrelated data which isn't really what we're dealing with here.[/quote]

[thumbs up], but when using the same queries against the base table, the query time was acceptable vs use against the view. Anyway, good point though; maybe i should start my own thread about the experience. Smile

[quote author="jonnyjon" date="1204747782"]Thanks for all the great replies guys. Good to hear I can use only 1 record per association.[/quote]

[thumbs up]


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.