ttocskcaj Posted December 26, 2010 Share Posted December 26, 2010 I'm working on a Social Network from scratch. I'm stuck on how to store friend relationships between users? On one topic here, I found an idea. Have a table with 3 columns; id,user_1,user_2 each relationship would have a new row in the database. Would this be the best way to do this? I can imagine the table would get quite big. If each user has 100 friends and there's 10000 users that's 1000000 rows. etc Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/ Share on other sites More sharing options...
revraz Posted December 26, 2010 Share Posted December 26, 2010 That probably would be the best way to approach it. Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1151507 Share on other sites More sharing options...
the182guy Posted December 26, 2010 Share Posted December 26, 2010 I also agree that is the best way to do it. 1,000,000 rows is not massive really in terms of RDMS. Especially when the row of data is just a PK and two FKs, it won't take up much storage and the database engine will have no problem searching and joining that table. Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1151535 Share on other sites More sharing options...
ignace Posted December 27, 2010 Share Posted December 27, 2010 Personally I think you should lose the ID, user_1 and user_2 should be sufficient Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1151829 Share on other sites More sharing options...
revraz Posted December 28, 2010 Share Posted December 28, 2010 I can see more advantages to having a row id than not having one. It's auto incremented, what can it hurt? Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1151975 Share on other sites More sharing options...
BlueSkyIS Posted December 28, 2010 Share Posted December 28, 2010 yes, you might want that primary key later. Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1151977 Share on other sites More sharing options...
ignace Posted December 28, 2010 Share Posted December 28, 2010 I can see more advantages to having a row id than not having one. It's auto incremented, what can it hurt? The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table: CREATE TABLE compound ( user .. friend .. .. FOREIGN KEY (user, friend) REFERENCES friends (user, friend) ); Don't add an ID just for the sake of having an ID in each table. On a side note ID's are not by definition INT UNSIGNED NOT NULL AUTO_INCREMENT they could aswell be a VARCHAR, just so you know Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1152040 Share on other sites More sharing options...
ttocskcaj Posted January 5, 2011 Author Share Posted January 5, 2011 Cool. Thanks for the suggestions guys Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1155086 Share on other sites More sharing options...
BlueSkyIS Posted January 5, 2011 Share Posted January 5, 2011 I can see more advantages to having a row id than not having one. It's auto incremented, what can it hurt? The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table: Good insights! I always add a primary key, thinking that's just the way it's done. Obviously not so. I gain another gram of knowledge! Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1155228 Share on other sites More sharing options...
davelearning Posted January 5, 2011 Share Posted January 5, 2011 I would add a field like 'active' if user2 accepts user1's request Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1155246 Share on other sites More sharing options...
ignace Posted January 5, 2011 Share Posted January 5, 2011 I can see more advantages to having a row id than not having one. It's auto incremented, what can it hurt? The compound user_1 and user_2 is unique why add more unique values to something that's already unique? Adding the extra ID shows a lack of DB knowledge as you can easily reference a compound key from another table: Good insights! I always add a primary key, thinking that's just the way it's done. Obviously not so. I gain another gram of knowledge! Glad I could help Quote Link to comment https://forums.phpfreaks.com/topic/222662-best-way-to-store-friends-in-a-database/#findComment-1155295 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.