To: pgsql-sql@postgresql.org
From: Mark Stosberg <mark@summersault.com>
Subject: Peer-review requested of soft-delete scheme
Date: Tue, 16 Apr 2013 12:24:00 -0400
Lines: 39
Message-ID: <kkjtv3$a75$1@ger.gmane.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
	rv:17.0) Gecko/20130329 Thunderbird/17.0.5
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org


Hello,

I'm working on designing a soft-delete scheme for our key entity-- there
are 17 other tables that reference our key table via RI. Let's call the
table "foo".

I understand there are a couple common design patterns for soft-deletes:

1. Use a trigger to move the rows to a "tombstone table".
2. Add an "deleted flag" to the table.

The "tombstone table" approach is out for us because all the RI.

The "deleted flag" approach would be a natural fit for us. There's
already a "state" column in the table, and there will only be a small
number rows in the "soft-deleted" state at a time, as we'll hard-delete
them after a few months. The table has only about about 10,000 rows in
it anyway.

My challenge is that I want to make very hard or impossible to access
the soft-deleted rows through SELECT statements. There are lots of
selects statements in the system.

My current idea is to rename the "foo" table to something that would
stand-out like "foo_with_deleted_rows". Then we would create a view
named "foo" that would select all the rows except the soft-deleted views.

I think that would make it unlikely for a developer or reviewer to mess
up SELECTs involving the statement.  Inserts/Updates/Delete statements
against the table are view, and coud reference the underlying table
directly.

Is this sensible? Is there another approach to soft-deletes I should be
considering?

Thanks!

    Mark


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql