[Dbix-class] "soft deletion"

[Darren Duncan]

fREW Schmidt wrote:
> I am sure there is a better term for this, I just don't know what it 
> is.  I would like for my database to have some type of removal field 
> that would effectively mark a row as deleted without deleting it.  I've 
> seen ORMs that do this before and I was wondering if DBIC did something 
> or had support for something like this.
> 
> To make myself more clear, I'd like either a binary flag or a date which 
> would probably be called deleted or date_deleted or is_deleted (feel 
> free to give input in this) and I'd like it not to show up in regular 
> resultsets unless I explicitly say I want to see the deleted rows.

Following up on other replies you got ...

What I would do is something a little more generic, which is often called 
auditing, and often makes use of a single extra table per normal table.

So the normal table holds only the current versions of live records and no 
deleted records, and the audit table has copies of all the records that used to 
exist as well as prior versions of changed records, and often for completeness, 
also a duplicate of all inserted and current records.  Note that a record update 
could also be conceptually a delete followed by an insert.

The point is that if your reason for a soft delete is to provide a way to undo 
something destructive, then a delete isn't the only common destructive action, 
but an update is too (and sometimes, so is an insert).

When I have done this before, generally only the live table had unique keys etc 
on it as usual, and the corresponding audit table did not.

In one form, an audit table for table <foo> might have a name like 
"<foo>_audit", or pick some other suffix you don't use elsewhere, unless your 
database supports multiple schemas/namespaces in which case you might use the 
same name in a different schema.

Also if you want to easily associate changes to multiple tables as an atomic 
database change, I also use a separate master table called audit_change_set, 
which at least has fields audit_change_set_id (a generated ordered unique id) 
and audit_date; if you track other things like who made the change that can be 
extra fields here.

Then in each table's audit table there are all the fields in the normal table 
plus 2 more, audit_change_set_id and audit_action_type; the latter containing 
one of [I,U,D].  For 'I' and 'U' the other fields are a copy of the record post 
insert/update, and for 'D' the other fields are a copy prior to delete; season 
to taste.

The audit tables are populated effectively by triggers on the main tables. 
Other than that, your normal code can just interact with the main tables without 
knowing about the added complexity of the audit, since no structure changes 
exist in the main tables.

Using the audit tables to either display history or to recover the database or 
individual records to an earlier state is left as an exercise to the reader.

Now I'm sure there are other good solutions, but this is what I did on the last 
project I had that did change tracking for recovery or 'history' queries.

I also expect to make some changes to that design on my next project, probably 
making the structure more resemble a version control system like Git, where the 
database has versions and each one knows what its parent versions are.  Now this 
isn't useful for everyone, but for my application it would be, which involves 
people making requests for data changes and others approving or rejecting those 
changes for public consumption possibly with edits.

-- Darren Duncan