CODEWITHSUNDEEP
javamysqltransactionsforeign-keysinnodb
hypercube
hypercube

Reputation: 1118

MySQL delete from table or update table column to mark it as deleted?

Problem details. Let's assume we have some InnoDB table and this table contains some records. My question is: how would you implement the "delete" operation, having in mind some form of operation logging? I quoted "delete" because there are some flavors to it:

  1. The delete operation should use a DELETE FROM table_x WHERE.... This works great with foreign keys constraints. But how can one keep a track of this operation? (by that i mean some form of database logging operation). If the deleted records are inserted in a secondary database or a different table on the same database would be a good idea?
  2. Another idea would be to have a column, let's say valid with possible values 1 (for active/valid) and 0 (for deleted records). A "delete" operation will simply set the value on this column to 0, and the business logic does not consider it anymore. Logging would be done on the same database, by preserving all the records. This approach will have to make heavy use of triggers to perform additional tasks, like updating records from another table, based on the foreign keys.

Feel free to add additional methods for accomplish this task, if you have them. Also, if we'll decide that variant 1) works best, is it possible in the same transaction (started from java code) to perform both DELETE FROM database1.table and INSERT into database2.table? (I honestly didn't tried this, but i don't think it will work). I just don't wanna end up with deleted rows from first database and missing rows from the 2nd. If not possible, but still 1) the best option, "moving" the data from table1 to table2 by deleting records from table1 and inserting them into table2 in the same transaction is a good practice?

Please have in mind MySQL server built-in checks that keep the database integrity and the fact that the solution will have to solve the logging problem also when answering my question/propose a solution.

Upvotes: 0

Views: 905

Answers (2)

Fseee
Fseee

Reputation: 2627

Generally I think a fisical elimination of a record is never a good idea.

A "logic" elimination like flagging a record as non-valid would be the best idea. In fact in the "real world", records of larges and important databases are never fisically deleted. I prefer the second approach because you can keep track of all of your database operations.

Upvotes: 0

Amir Pashazadeh
Amir Pashazadeh

Reputation: 7322

I prefer the first approach, it helps you with reference integrities and ..., you can handle it with a trigger on base table, so everything happens in a the same transaction

Upvotes: 1

Related Questions