Focusing on a project right now and we must implement soft deletion for almost all customers (user roles). We made the decision to include an "is_erased='0'" area on each table within the database and hang it to '1' if particular user roles hit a remove button on the specific record.

For future maintenance now, each Choose query will have to ensure they don't include records where's_erased='1'.

It is possible to better solution for applying soft deletion?

Update: I ought to also observe that there's an Audit database that tracks changes (area, old value, new value, time, user, ip) to any or all tables/fields inside the Application database.

You can perform all your queries against a view that consists of the Where'sErased='0' clause.

I'd lean for the "Rails way" having a erased_at column that consists of the datetime of once the deletion happened. Then you definitely get some free metadata concerning the deletion. For the Choose just get rows WHERE erased_at IS NULL

Getting is_erased column is a pretty good approach. If it's in Oracle, to help increase performance I'd recommend partitioning the table by creating a listing partition on is_erased column. Then erased and non-erased rows will physically maintain different partitions, though for you personally it will be transparent.

Consequently, should you type a question like

Choose * from table_title where'serased = 1

then Oracle will work the 'partition pruning' and just consider the right partition.

when the table is big and gratifaction is definitely an problem, you could move 'deleted' records to a different table, that has information like duration of deletion, who erased the record, etc

this way it's not necessary to add another column for your primary table

The very best response, sadly, is dependent on which you are attempting to accomplish together with your soft deletions and also the database you're applying this within.

In SQL Server, the very best solution is always to make use of a erased_on/erased_at column with a kind of SMALLDATETIME or DATETIME (with respect to the necessary granularity) and also to make that column nullable. In SQL Server, the row header data consists of a NULL bitmask for each one of the posts within the table therefore it is marginally faster to do an IS NULL or perhaps is NOT NULL than to determine the value saved inside a column.

For those who have a sizable amount of data, you will need to consider partitioning your computer data, through either the database itself or through two separate tables (e.g. Items and ProductHistory) or with an indexed view.

I typically avoid flag fields like is_erased, is_archive, etc simply because they only carry one bit of meaning. A nullable erased_at, aged_at area offers an additional degree of meaning to yourself and also to whomever gets the application. And That I avoid bitmask fields such as the plague given that they require an awareness of methods the bitmask was built to be able to grasp any meaning.

That is dependent on which important information and what workflows you need to support.

Would you like to have the ability to:

  • understand what information was there (prior to being erased)?
  • know if this was erased?
  • know who erased it?
  • know with what capacity these were acting once they erased it?
  • have the ability to not-remove the record?
  • have the ability to tell if this was not-erased?
  • etc.

When the record was erased and not-erased four occasions, could it be sufficient that you should know that it's presently within an not-erased condition, or would you like to have the ability to tell what went down within the interim (including any edits between successive deletions!)?

You'll certainly have better performance should you move your erased data to a different table like Jim stated, in addition to getting record of if this was erased, why, by whom.

Adding where erased= to any or all your queries will slow them lower considerably, and hinder using some of indexes you might have up for grabs. Avoid getting "flags" inside your tables whenever you can.