Re: Newbie question on table design.

On Apr 26, 6:48 am, somedevelo..._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.

Huh..Chances are that if you need to do that it simply means that your logical schema does represent thruthfully enough a segment of reality. Historising tables in any form of shape is a hack. If a DBMS requires so much operations to historize information what's the point using it in the first place? Received on Thu Apr 26 2007 - 16:09:01 CEST