What are best practices for removing "friends" from a database?

Question

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 1

I'd say delete it. Unnecessary data to confirm what will be the default state doesn't really serve much point, especially with the amount of these even a smallish site would likely accrue. If you're going to store useful data extra in there, maybe just change, but otherwise there's little point not to.

Answer 2

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 3

typical N:M relationship:

You dont need friendship_id and are_friends fields in the relationship table. This way if the relationship in the table is present then users are friends, otherwise not.

Storing the "not friends" is a useless clutter. Unless the case described by @fencliff

And whatever you do: never reset the auto-increment unless you reset the whole database.

Answer 4

I'd say that this depends first of all on your business needs. If you need to keep trace of the fact that these two people where friends once and are not any more then you would have to keep the row in your db, possibly also adding some more columns you might be interested in (when they became friends, when they stopped being friends, etc.). Otherwise, you don't really need this row so it's better to delete it. Of course in that case you also don't need the are_friends column since all the rows existing in your db will correspond to people being friends.

Answer 5

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.