Home -> Usenet -> comp.databases.theory -> Newbie question on table design.

Newbie question on table design.

From: <somedeveloper_at_gmail.com>
Date: 25 Apr 2007 22:48:41 -0700
Message-ID: <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 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 - 07:48:41 CEST












Original text of this message