hi.. i need suggestion here, as I am double minded can cannot decide.. here is what an trying to acheive, I am making a social network script, where a person adds another person as friend, a requets is generated adn the request status is set to "unconfirmed" once the other person confirms, the status of request is changed to "confirmed".. I am using single table to maintain "friend requests" and "friends", as I dont think maintaining 2 table will be effecient.. now the question is, when a user confirms another user, a new query is run so that THIS USER (who is confirming friend request) is added to the friends table (and appears on requesters friend list), in this scenario, if my database of users reaches 1 million, then I will have 2 million in "FRIENDS" table.. I think it would be a problem for larger website, any suggestion, how do achieve this, I was thinking to add the friends to a single row with commas like, if a person has 3 friends, I should mainatain 1 row for that person add add his friends IDS like 1,2,3 in a single field, but again if the erson has 2K friends, another big deal ? what would be the MOST effecient to achieve this ? thanks for your time..
Why not just one table for members which ahve one field called friends defined as a list of ids. friends TEXT -- list of ids: 1,2,3,4,5 to retrieve friends, you just do a find_in_set ...
A good database will be able to cope with millions of records and if you get that many members it will be time for a couple of dedicated servers. I'd have a separate table for friends with a status field to indicate if they are a friend or it's just at the request stage. Table1 - Members memberID memberName Table 2 - Friends id dateAdded memberID1 memberID2 status At later stages when you'll want to cache your pages so that you are not hitting the DB all of the time.