What is the best friend-list table design (for performance)? Probably there should not be many rows for every frienship. What Facebook and Myspace is doing? (in MySQL)
+3
A:
Assuming you have a separate users table, it would just be a table where each row has two user ids (e.g., user, friend) and possibly a third column to indicate the strength of the relationship (BFF, acquaintances, etc). You can add unique constraints to make sure you don't have duplicates where the same pair is saved as (user, friend) and (friend, user).
Tom
2010-01-28 01:07:30
A:
mY CURRENT FRIEND TABLE STRUCTURE
————————————————
frnd_id | user_id | frnds |
————————————————-
The first column saved the auto generated id. the next column “user_id” saves the value of the user and the last column saves the string with IDs of other users containing “|” as a separator. I was able to search users though and was also able to add them to a user. But the problem starts when user searched and I’m unable in not showing user the same friends that he has in his list.
Please suggest me a better option. IS MY DATABASE STRUCTURE CORRECT?
bluepicaso
2010-05-07 16:58:04
This isn't technically an answer. It should probably be a separate question. Regardless, I would recommend dropping the `frnds` column and stop auto-generating the `frnd_id` column. For each friendship, simply store the user's `user_id` in the `user_id` column and their friend's `user_id` in the `frnd_id` column. If you want a two-way relationship, you can run this query: `SELECT frnd_id FROM FRIENDS WHERE user_id = :MY_USER_ID: UNION SELECT user_id AS frnd_id FROM FRIENDS WHERE frnd_id = :MY_USER_ID`
Adam Paynter
2010-05-07 17:23:33