Rick Joe Rick Joe - 1 year ago 74
SQL Question

move or set as 1 deleted records?

my question here is, what is the best solution to delete an user comment? (and keep it for legal reasons)

when he clicks in delete I have two possibilities:

1) copy to another table: create a table identical in structure (

), copy the comment there and delete from

2) set deleted as 1: update table comments, set deleted = 1. so every select I need to add in where:
where deleted=0
to get non-deleted ones.

Should I use 1 or 2?

Answer Source

While I would consider the visible difference between the two insignificant solution 2 would be the more 'true' method of the two.

Solution 1 involves two modification queries, that is to create a row in the del_comments and then delete the row in the original table, for what you would think would be easier for MySQL to search through. However one could argue "Why have one single table full of comments for every thread when we could just have a table of comments for every individual thread".

Solution 2 involves a single modification query, that is to switch a boolean on an existing row, at the perceived cost of MySQL having to compare every row's deleted column to 0. However the entire point of SQL is to store rows of data and columns of data relevant to that, so this solution would be true to the reason you're using an SQL database in the first place. You're almost certainly doing queries that are a lot 'heaver' that simply checking for rows where deleted is 0.

I would argue that solution 1 would be less memory and space efficient but that's purely anecdotal, but I can tell you for a fact that it's a lot easier and a lot more simple for the database structure to use solution 2.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download