CODEWITHSUNDEEP
mysql
Alex
Alex

Reputation: 1486

MySql Soft delete

I have an existing application (with MySQL DB).

I just got a new requirement where I need to delete some records from one of main entity. I dont want to apply hard delete here as its risky for whole application. If I use soft delete I have to add another field is_deleted and because of that i have to update all my queries (like where is_deleted = '0').

Please let me know if there is any smarter way to handle this situation. I have to make changes in half of the queries if I introduce a new flag to handle deletes.

Upvotes: 0

Views: 4536

Answers (2)

dani herrera
dani herrera

Reputation: 51715

Your application can run without any changes. MySQL is ANSI-SPARC Architecture compliant . With external schema you achieve codd's rule 9 "Logical data independence":

Changes to the logical level (tables, columns, rows, and so on) must not require a change to an application based on the structure. Logical data independence is more difficult to achieve than physical data independence.

You can rename your tables and create views with original table names. A sample:

Let's supose a table named my_data:

REMAME TABLE my_data TO my_data_flagged

ALTER TABLE my_data_flagged 
   ADD COLUMN is_deleted boolean NOT NULL default 0;

CREATE VIEW my_data AS 
   SELECT * 
   FROM my_data_flagged 
   WHERE  is_deleted = '0'

Another way is create a trigger and make a copy of erased rows in independent table.

Upvotes: 5

Michael Fredrickson
Michael Fredrickson

Reputation: 37398

Four suggestions:

  1. Instead of using a bit called is_deleted, use a dateTime called something like deleted_Date... have this value be NULL if it is still active, and be a timestamp for the deletion date otherwise. This way you also know when a particular record was deleted.

  2. Instead of updating half of your queries to exclude deleted records, create a view that does this filtering, and then update your queries to use this view instead of applying the filtering everywhere.

  3. If the soft deleted records are involved in any type of relationships, you may have to create triggers to ensure that active records can't have a parent that is flagged as deleted.

  4. Think ahead to how you want to eventually hard-delete these soft-deleted records, and make sure that you have the appropriate integrity checks in place before performing the hard-delete.

Upvotes: 4

Related Questions