Newbie question on table design.

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.

.

Relevant Pages

  • Re: Newbie question on table design.
    ... slightly more involved SQL; ... far less space overhead as only very rare, ... Besides you may be surprised by the physics after all. ... additional cost. ...
    (comp.databases.theory)
  • Re: Newbie question on table design.
    ... application allows logical deletion of a small fraction of these ... slightly more involved SQL; ... far less space overhead as only very rare, ... DBMS requires so much operations to historize information what's the ...
    (comp.databases.theory)
  • Re: Newbie question on table design.
    ... application allows logical deletion of a small fraction of these ... slightly more involved SQL; ... far less space overhead as only very rare, ... 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. ...
    (comp.databases.theory)