2

Scenario:

  • I have a simple text-based post. Which can receive comments and likes. All entities have their own tables.
  • Like and comment generate notifications.

Basic Parent-Child RelationShip in database (RDBMS):

enter image description here

Current Approach:

When I have to delete post, I delete comments, likes and their notifications too.

Because:

  • when I fetch only notifications I don't have to check comment, like or post status to filter out deleted entity notifications.
  • when I access a single comment (for edit or delete), I don't have to check post's status.

This approach helps me in GET data fast, but it makes deletion process slower. And because the use of transaction, it locks the table when there is too much activity on the post.

Question:

Should I stick to what I am doing currently? or I should try to delete fast, and perform checks on GET data? or any other approach?

PS: I am not actually deleting. I am soft deleting, means update a column named status to DELETED with deleted time. To keep it for few days, then permanently delete using job.

Improve this question
12
  • 1
    Do you have actual performance problems in a real-world program with the deletion, or are you just asking because you fear you might get some issues?
    – Doc Brown
    Apr 10, 2017 at 6:35
  • Are your post, comments etc. cached in memory or present only in database? In databse, are their foreign key constraints? If constraints are present, are you worried that deleted cascade will slow down performance?
    – Manoj R
    Apr 10, 2017 at 6:56
  • 1
    @ManojR Present only in database. Constraints are present, but I am not actually deleting. I am soft deleting, means update a column named status to DELETED. I should've mentioned it earlier..
    – Shaharyar
    Apr 10, 2017 at 7:18
  • 1
    So your deletion is actually an update, setting status to "deleted", and that operation makes a full table lock? Seriously??? What database system are you using? Or is your asynchronous "delete job" causing the issues?
    – Doc Brown
    Apr 10, 2017 at 8:03
  • 1
    @Shaharyar well, cursors can be useful, but only in rare cases where you need to do something unusual and complex. SQL beginners often over-use cursors because they are similar to more familiar programming paradigms that involve looping through data.
    – user82096
    Apr 11, 2017 at 5:51

1 Answer 1

Reset to default
5

If you are only soft deleting the post, I wouldn't do anything other than mark the post as deleted.

There is no need to mark other content as "deleted", since it's associated with a deleted post. Keep things as simple as possible.

And why bother to delete the other stuff at all (even after a few days)? Deleted content might be useful in the future:

  • If something was accidentally deleted, you can restore it.
  • You might want to analyze past use of your website.
  • If any bug in your site is discovered, analyzing the old data might help you characterize it.

If you were to have concerns related to the size of the data in the future, then you could run a job to delete items (or archive them to a separate location). But I wouldn't do this until the size actually becomes a problem.

Update: the performance problems you were experiencing were caused by inappropriate use of cursors. If you simply use update statements on each table to mark the content as deleted, your original design should perform well. It may be easier to simply adapt your stored procedure that deletes a post so that it is more efficient, rather than changing your data and design.

Improve this answer
6
  • But I'll have to check for post status every time I access any child. Won't it cost an extra check while accessing a child? Right now I only access child and see its status only.
    – Shaharyar
    Apr 10, 2017 at 19:18
  • @Shaharyar The idea is if the parent is the only thing that gives the children visibility then marking the parent deleted is enough.
    – candied_orange
    Apr 11, 2017 at 1:07
  • @CandiedOrange Hmm. Let's suppose I delete the user, so I shouldn't delete all of his posts, but only delete the user and stop the access to his posts. That should be the approach?
    – Shaharyar
    Apr 11, 2017 at 5:34
  • @Shaharyar, basically, I would think any action related to a comment, like, or notification can only happen when a post is not deleted. A user can't add or edit a post, or like a post, if that post has been deleted. Notifications (presumably) are driven by any changes within comments or likes. And this, again, can't happen on a deleted post. So, none of these other tables need a separate marker for whether the content is deleted. Because the content related to a deleted post simply won't get used.
    – user82096
    Apr 11, 2017 at 5:45
  • @dan1111 yeah I get the point. I've separate API endpoints for all child entities, I don't check parent's status when they're called (i.e. a comment update API lets suppose). So this is the reason I was deleting top to bottom. I'll have to change this approach.
    – Shaharyar
    Apr 11, 2017 at 5:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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