What are best practices for removing "friends" from a database?
Question by Josh Smith
Say you have a table schema such as this: friends(friendship_id, uid1, uid2, are_friends).
When one friend un-friends another person, is it best practice to delete that row from the database (and reset auto-incrementing)? Or should you simply change the are_friends status to ‘N’?
I’d love to know what you think and why.
EDIT: My primary concern is showing some information about the state of the friends, and also showing who friended who. That way at the time of the add I can notify the friended of the friender’s request and yet not have them be friends yet.
I had originally had are_friends as a large ENUM with more options. But in that ENUM I had options such as removed and blocked, and I wasn’t sure whether that was truly useful data or not. Any further thoughts on the merits of having more vs. less data?
Also, an added question I’ve had since I posted is how one should avoid duplicating friendships. Should you make (uid1, uid2) a primary key?
Answer by fencliff
Is there any data associated with the friend record you might want to restore, if the user re-friends the other user?
If you want to track their history, or keep some metadata associated with their relationship, then keep the record.
If you don’t want to track their history, and re-friending later doesn’t require user to input any extra data, delete it. You’ll have smaller tables, easier queries, and decreased likelihood you’ll ever forget that where-clause and show the user friends they’ve already deleted.
Answer by Starx
I would suggest to remove the row, because first thing is first, it is unnecessary data to occupy space, and besides the next time the user decides to re-friend with the same friend, then we can enter the data again, if needed.
