ijazktk Posted August 19, 2009 Share Posted August 19, 2009 hi all, i am writing as i am stuck at a point,i do have a couple of solutions but for optimization purpose and to minimize the time i need a better one [problem] Like i have signed in to my profile in social networking website my profile is named K and i want to add mr Y in my friend list, i click on "add as a friend on mr Y's profile and he is now seeing my friendship request" after accepting my request i am added to his friend list. [possible solution one] one solutions is that i made a table like the one [id] [from] [to] [status] 1 K Y 0 (request pending) 2 Z Y 1 (added as friend) . .now the table has data where K has sent request to Y and is not a friedn yet and the status is 0, similarly in case id 2 we have a friend added with MR Z and status 1 mean(added) 0 means pending so a simple query like "select all from table where [to]=Y and status = 0" will generate a request list for Y and the same query with a status of 1 will generate a friend list for Mr Y. [possible solution two] that i split the table into two more tables named as TO and FROM and in that case in FROM i have the PROFILE table ID as a foreign Key and in TO table i have FROM's Table primary key(F_ID) as a a foreign key. structure of profile table [id] [name] [.....] [...] . . ... .. 14 X .. .. structure of FROM table P.K F.K P.K= Primary key and F.K = Foreign Key [F_ID] [id] .... 1 14 .... structure of TO table F.K [T_ID] [F_ID] status 7 1 0 now a query like "select T_ID from TO where T_ID =7 and F_ID=FROM.F_ID and TO.status=0" will generate a friend list. [the confusion] but here i puzzle coz the TO table has structure as the one in case one except that in from table i will not face repetitions but in TO i will have repetitions. i will request the pro members to kindly solve this problem for me and suggest me the best method which can work in no time for million of records. if there is any other solution plz do let me know. thanks and regards ijazktk IT Officer Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 19, 2009 Share Posted August 19, 2009 Hi Looking at that in the first case you will have to have duplicates, as I would assume that once Y has confirmed the friend request from K then an extra row would need to be inserted with a from of Y and a to of K (the alternative would appear to be to joining 2 SELECTs, one to check the from and one to check the to). I am not that clear on what you mean with the 2nd case. All the best Keith Quote Link to comment Share on other sites More sharing options...
ijazktk Posted August 19, 2009 Author Share Posted August 19, 2009 the other is a bit weired but it have on advantage over the first that it dont have duplicate entries in FROM table only. about the joins if u can give me a little clue then i can pick a logic out of it. thanks in advance and also for the reply Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 19, 2009 Share Posted August 19, 2009 Hi My assumption is that if K goes to be a friend of Y then when approved not only will K be a friend of Y but Y will be a friend of K. You could go from:- [id] [from] [to] [status] 1 K Y 0 (request pending) 2 Z Y 1 (added as friend) to [id] [from] [to] [status] 1 K Y 1 (added as friend) 2 Z Y 1 (added as friend) 3 Y K 1 (added as friend) and so just select tofrom for the friends of K. Or if you went from:- [id] [from] [to] [status] 1 K Y 0 (request pending) 2 Z Y 1 (added as friend) to [id] [from] [to] [status] 1 K Y 1 (added as friend) 2 Z Y 1 (added as friend) you would need something like:- SELECT `to` AS FriendOf FROM SomeTable WHERE `from` = 'K' AND `status` = 1 JOIN SELECT `from` AS FriendOf FROM SomeTable WHERE `to` = 'K' AND `status` = 1 Hope that makes sense. If doesn't seem logical that K can be an approved friend of Y without Y being an approved friend of K. All the best Keith Quote Link to comment Share on other sites More sharing options...
ijazktk Posted August 19, 2009 Author Share Posted August 19, 2009 yeah i totally agree with u both have to b frnds of each other...but here i want something that must not make load on website like if i have four person all add each other then its like user a b c d a with b a with c a with d b with a b with c b with d c with a c with b c with d d with a d with b d with c so for only for users i have 12 rows consider i have million then i thinks its worst kind of datbase..... any suggestion abt that problem.....coz its the simplest form i can implement...if its for some specific group of ppl but when i have unlimited users then it will make problem...if u think mysql gonna take no time picking a record out of million, and picking for thousand users then its ok i will put that...coz i m not database expert so i dont know abt sql behaviour on large scale Quote Link to comment Share on other sites More sharing options...
kickstart Posted August 19, 2009 Share Posted August 19, 2009 Hi Difficult to say with the volumes but keep things simple and index the tables well and you stand the best chance. All the best Keith Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted August 19, 2009 Share Posted August 19, 2009 First off abstract the friend's behavior into an encapsulated piece of PHP code with a consistent interface. Then you are free to change the underlying structure as much as you want with minimal impact to existing code that uses the interface. In regards to the database, MySQL is meant to handle millions of records and still be fast. If you get to the point of having millions of registered users, then you should be at a high enough point in ad (or some other type of) revenue that you can use a separate and more powerful database server. What you have is a many-to-many relationship and your first solution is the most common way to handle them. A small side note, make sure you use ints for your foreign keys and identifiers as ints will typically compare faster than strings. Quote Link to comment Share on other sites More sharing options...
ijazktk Posted August 21, 2009 Author Share Posted August 21, 2009 thnaks a lot for all of your time, i better go witht he first logic as its easy to implement, and i think that by the time the records are reacing to millions i will be in positino to handle it more efficiently...... wish u all success Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.