Re: Newbie question on table design.

I recommend the KISS approach. Disk space is cheap. Development isn't. Any time you split a relation horizontally or vertically, the constraints needed to maintain integrity change--sometimes in ways that are not supported by the DBMS. For example, splitting a relation horizontally requires the addition of a circular inclusion dependency. Splitting a relation vertically complicates the definition of key constraints and can really complicate interrelational dependencies. Furthermore, depending on the DBMS and the other columns in the table, the addition of a bit column doesn't add any space at all. In SQL Server, for instance, 2 bytes are allocated per row whether you have 1 bit column or 16.

Another solution would be to leave the rows intact in the original table and add an additional table that contains only the key columns from the original table for the deleted rows. All that would be needed to maintain integrity would be a simple foreign key constraint. You could then create a view that would exclude any row from the original table that also has a row in the other to simplify the queries that require is_deleted to be 0.

<somedeveloper_at_gmail.com> wrote in message news:1177566521.400997.267690_at_t39g2000prd.googlegroups.com...
> Hi,
>
> I have a table T with -- let's say -- a million+ records. The
> application allows logical deletion of a small fraction of these
> records. (Un-deletion of deleted records is allowed if you have admin
> privs via a Priveleges table.)
>
> I can easily think of 2 ways to support record un-/deletion.
>
> 1. Add an 'is_deleted' column that can take a binary 1/0 value (1 =
> deleted, 0 = not-deleted).
>
> This approach will result in...
> + simplest possible SQL;
> - more space overhead (for at least some if not all DBMSes, now or
> in future) due
> to addition of a full column for a million+ rows most of which
> will
> anyway hold a single value (namely, 0 = not-deleted).
>
> 2. Alternatively, create another Deleted_T table in which the records
> deleted from T would be inserted.
>
> This approach will result in...
> - slightly more involved SQL;
> + far less space overhead as only very rare, exception information
> ends up getting recorded.
>
> Presently, I tend to like #2 because...
> * I can live with the 'slightly more involved SQL';
> * I can live with the small speed overhead of consulting an
> additional table (Deleted_T);
> * I welcome every bit of free disk space on by server.
>
> I cannot foresee any other de/merits entailing these approaches. If
> you can, would you please point them out and/or recommend me your
> favorite.
>
> Thanks.
> A DBMS newbie.
>
Received on Thu Apr 26 2007 - 14:34:47 CEST