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 (
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.