2

For some reason, somebody told me never to delete any MySQL records. Just flag it with deleted.

For example, I'm building a "follow" social network, like Twitter.

+-------------+------------+------+-----+---------+----------------+
| Field       | Type       | Null | Key | Default | Extra          |
+-------------+------------+------+-----+---------+----------------+
| id          | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id     | int(11)    | NO   |     | NULL    |                |
| to_user_id  | int(11)    | NO   |     | NULL    |                |
+-------------+------------+------+-----+---------+----------------+

User 1 follows User 2...

So if one user stops following someone, should I delete this record? Or should I create a column for is_deleted ?

Dan J
  • 16,319
  • 7
  • 50
  • 82
TIMEX
  • 259,804
  • 351
  • 777
  • 1,080
  • 3
    It depends. Think to yourself; will this record _ever_ be needed _ever again_? If not, delete it. If you think it might, keep it. – Bojangles Nov 25 '11 at 23:31
  • It depends on whether you may ever need the deleted records - to restore them, or as evidence in a lawsuit, etc. – Pekka Nov 25 '11 at 23:31
  • possible duplicate of [`active' flag or not?](http://stackoverflow.com/questions/102278/active-flag-or-not) – Greg Hewgill Nov 25 '11 at 23:33
  • 2
    If you do it, consider doing a `deleted_on` instead of `is_deleted` column. Useful info. – ceejayoz Nov 25 '11 at 23:39

4 Answers4

5

This is a concept called "soft delete". Google for that term to find more. But marking with a flag is only one option - you could also actually perform the delete, but have a trigger which stores a copy in a history table. This way you won't have to update all of your select functions to specifically filter out the deleted records. Also, you won't have as much load on your table as you have to scan through the additional records littering your table.

Jake Feasel
  • 16,785
  • 5
  • 53
  • 66
1

Generalizing about the larger concept of "you should never delete records" would (and should) probably get this question closed as Not Constructive, but you've given a specific scenario:

User 1 follows User 2...

So if one user stops following someone, should I delete this record? Or should I create a column for is_deleted ?

The answer in your case depends on whether, after an unfollow, you ever again need to know that User 1 followed User 2. Some made-up, possibly silly, examples where this might be the case:

  • if it was desirable to change the text User 1 sees when electing to follow User 2 from "Follow User 2" to "Follow User 2 again? Really? Didn't you learn your lesson?"
  • if you wanted to show User 2 a graph of who (or, in aggregate, how many) followers they've had over time

If you don't need functionality that relies on the past state of users following each other, then it's safe to delete the records. No need to take on the complexity of soft delete when you ain't gonna need it.

Dan J
  • 16,319
  • 7
  • 50
  • 82
0

I wouldn't say, "never delete any MySQL records". It depends. If you want to keep track of user interactions you could do this by the use of delete flags. You could even create a seperate logging table which tracks each action like "follow" and "unfollow" with the appropriate user id's and timestamps, which gives you more information in the end.

It's up to you and depends on which data you want to store. And please consider the privacy of your users. If they want their data explicitly deleted, then do so.

Alp
  • 29,274
  • 27
  • 120
  • 198
-1

I have always been a fan of creating a blnDeleted field and using that instead of deleting a record. It is much easier to recover or add that data back in if you leave it in the database.

You may think you will never need the data again, but it is possible. Even for something as simple as tracking unsubscribes or something like that.

StephenCollins
  • 785
  • 4
  • 10