Re: Newbie question on table design.

somedeveloper_at_gmail.com wrote:

> 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.

Unless you are careful, you will run afoul of the _Principle of Orthogonal Design_.

You will increase your indexing costs but improve the effeciency of indexed lookups, which is a tradeoff not considered above.

Does T have any dependent tables referencing it? If so, you will either have to delete all the dependent values when deleting from T, or you will have a messy constraint to write to preserve integrity. Received on Thu Apr 26 2007 - 13:23:31 CEST