Search code examples
mysqlfeedcascading-deletes

Best way to delete a page with connected messages?

I have a page that I have created with a whole bunch of 'feed items' - like facebook - the problem I am currently facing is the best way to figure out 'delete logic'. That is, if a user deletes a PAGE - I want to remove the "connected messages" to this page that is:

  1. feed items
  2. comments
  3. etc

I have a number of MySQL tables that hold feedItems, comments etc - but I'm unsure of the most "optimal way" to remove all this if a user deletes a page ?

Would someone be able to advise ? I guess its similar to StackOverflow - if a question is delete, what's the best way to delete

  1. the question
  2. the answers
  3. all the comments

etc ?

Solution

  • If you're using InnoDB, the easiest way use the ON DELETE CASCADE on you foreign keys. This option makes MySQL delete rows in tables when the row to which the foreign key refers to is deleted.

    For instance, if you have a table "question" with a primary key ID, and a table "answers" with a foreign key "question_id", when the question will be deleted, all the rows in "answers" that contains the same question_id will be deleted too.

    Else you need to write multiple delete statements, one for each table.