1

I have a table of user relationship which stores who follows whom, the table structure is as follows

-id
-follower
-following

when someone follows any other user, I add a record with follower user id and id of user he is following, now my question:

When user wants to un-follow that user, I have decided rather than deleting the record I can add another column break at the end of this table, which I will set to true when user wants to un-follow, this way I can maintain consistency in the records, that is there won't be any gap like 1,2,4,7,8,9,15 if I delete records. If I don't delete the record, I can use it if that user want to follow that user again in future I can set break column of that record to false and the relationship will exist again. What do you think; should I use this approach or I should delete the record?

Tell me if the approach is good or there could be any issues associated with it and also tell me if there is any better way you know.

  • 4
    Why even have the ID column in a links table? Does your database not allow PRIMARY KEY (follower, following) to keep the two unique together? – Izkata Apr 7 '13 at 18:13
  • @Izkata you are right, I dont need ID column here, thanks +1 – vikas devde Apr 7 '13 at 19:02
  • But I haven't made them primary key, I have users table which contains all user data...these follower and following ID's are referencing to the user id in the users table – vikas devde Apr 7 '13 at 19:04
  • UNIQUE (follower, following), then, if that makes more sense for you. I tend to use pkey in that situation, though, since that combination is the key for that table. – Izkata Apr 7 '13 at 20:16
  • @vikasdevde what is being suggested is not removing the foreign key relationships, but saying that this type of table is entirely defined by the pair of (follower, following), and you don't need anything else to identify a row. A column can be in both a foreign key relationship with another table, and part of the primary key relationship of the table it is in – Caleth May 25 '16 at 16:02
6

In general flagging records as inactive isn't uncommon and would work as you describe it, you often find flags like "deleted" when there is a good reason to keep the information that something existed at some time, for example there are other records that relate to its id.

An alternative way would be a date field that is null unless deletion, in case you need to know when deletion happened.

But in your special case it seems useless to keep old records. Why would you worry about gaps in the id? Happens all the time.

1

What you're considering by having an unfriend field (I prefer the idea of it being a date) which is like a soft delete. It would allow you to maintain other tables that rely on the ID of this relationship table.

A functional approach could be used where another record is added for the same two user relationship, but a field indicating it was broken. This way you never update nor delete a record, just keep adding new ones like a log.

Do whatever causes the fewer problems for the rest of your data if possible. Only you can tell which will have better performance or create any type of replication problems.

I don't understand the whole idea behind keeping the sequence numbering. So what if there are gaps; there shouldn't be any logic/meaning applied to this field other than uniqueness and it could be the foreign-key in another table. Relying on the number for any other reason, is going to cause trouble.

0

I would keep this tables as is and have a separate log table (with dates). reason being that its just a a matter of time that you want to track Friend-unFriend-FriendAgain event chains

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.