It’s a classic problem in application design – you’ve got a nicely normalized database, lots of tables with related records. You need to allow the user to delete a top-level object – which OUGHT to produce some cascading deletes through a bunch of tables. BUT! – you don’t trust the user. So you need to support some kind of undelete, some kind of audit trail. What to do?

1. Have another table for each table, and create “deleted” records in there.
2. Have a “deleted” column on each table.

Both have their challenges. When rapid prototyping, the first one is cumbersome because you need to remember to make data model changes in two places, ensuring data integrity between current and deleted data, etc. But the second one would seem to require an extra WHERE clause for every SELECT statement in the entire application.

MySQL 5.0 comes to the rescue with support for VIEWS. Simply define a mirrored view for each table, such that:

CREATE view bounties as SELECT * from bounty where deleted = 0;

Obviously CRUD needs to be executed against the underlying table, but now you can safely declare simple SELECT statements everywhere.

I’m considering a similar approach, using nested VIEWs, to address Adult Filters. I’ll let you know how that goes.

Blogged with Flock