Whats the best way to handle Member to Member associations.

[monkeytooth]

Well I know the title isn't all to descript. So Ill try here. What I mean by member to member associations is. I am developing a system where my existing member base can befriend one another and have certain privileges if they share a connection vs if they don't. My issue is however whats the best way to store those associations. Should I have on a per member basis a single table with a couple columns per row, where one of the columns is a LONGTEXT that I can store a serialized array or some form of arrays worth of data in, or should I have a table where one row is one members id, the other row is the other members id and maybe a numeric switch of sorts that defines said association as valid/pending/otherwise. I dont expect my members to befriends 10s of thousands of other people on the site but I expect each member possibly getting into the range of several hundred shared friendships per user.

 

I think I would go like facebook and issue a limit 5,000 max friends (maybe as high a number as that sounds) but who knows maybe I wont have a cap. Also I am intent on doing similar connections, hey your 2 friends are friends with this person do you know this person as well.. So all in all I am trying to figure out what the best way is to handle data in a situation like this, that won't in time put a massive load on the DB,

 

Im thinking my best bet is row to row like

  member1 | member2 | active | datetime

 

but in all im looking for suggestions.

[kickstart]

Hi

 

A table with 1 row per link would be best. This way you can easily join the members table against the link table and back to the members table to get details.

 

All the best

 

Keith