[Dbix-class] "soft deletion"

[Ash Berlin]

On 16 Feb 2009, at 23:23, Darren Duncan wrote:

> 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

Not sure if someone has mentioned this already or not, but http://search.cpan.org/~nuffin/DBIx-Class-Journal-0.900001_02/ 
  does some/most of the things you suggest.