I am currently creating a platform which allows people to make "friends" on the site. I was going to create a table with the ID of the user and the ID of the friend, creating a relationship. I see this becoming a huge table however, and performance will degrade. I now want to store all of the friends user ID's in one table row, seperated by commas. EX. MyID = 23 MyFriendsIDs = 45,6756,78567,8968,8474 etc. Is it possible to call a recordset loop to select from that table and get the record for each of those specific friend user ID's? I hope that makes sense... thanks for any help!
This generally a bad way to do it. The best method would be to make 2 tables, one with the user info in it, and the other only for connecting a user to their friend. Basically: Users: user_id user_name Friends: id user_id friend_id You would store each relationship in the the friends table. Each user can have an unlimited number of friends. Make sure that the user_id and the friend_id have indexes on the Friends table. Otherwise this is a much better way of accomplishing this than storing a bunch of id's in csv format in a single field.
Thank you very much for the suggestion! I have done such and created a relationships table. I am managing multiple relationships (friends, fam, co workers etc..) so this may be the best option. I will set the relationship id as well as primary key, myid and friendid. I was worried about the table becoming too large, but after a little reading, my worries have been put to rest. Thanks again for the help!
It can become large no doubt, but the actual space will be very low and with the correct indexes, it should be very efficient. Personally, I would use this sort of setup with all data that has a similar relationship. The csv idea makes a smaller database overall, but requires a lot more resources to parse out and then re-query each number in the field. In the end, it's very inefficient and normally results in many more queries and overhead. Also, a good idea is to put a foreign key relationship on the user_id, and friend_id of the Friends table so that the id's in that table must exist in the main table.