Path: news.f.de.plusline.net!news-fra1.dfn.de!news.tele.dk!feed118.news.tele.dk!postnews.google.com!r35g2000prh.googlegroups.com!not-for-mail
From: Cimode <cimode@hotmail.com>
Newsgroups: comp.databases.theory
Subject: Re: Newbie question on table design.
Date: 26 Apr 2007 07:09:01 -0700
Organization: http://groups.google.com
Lines: 48
Message-ID: <1177596541.451624.225720@r35g2000prh.googlegroups.com>
References: <1177566521.400997.267690@t39g2000prd.googlegroups.com>
NNTP-Posting-Host: 195.154.188.107
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1177596548 4450 127.0.0.1 (26 Apr 2007 14:09:08 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 26 Apr 2007 14:09:08 +0000 (UTC)
In-Reply-To: <1177566521.400997.267690@t39g2000prd.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.3) Gecko/20070309 Firefox/2.0.0.3,gzip(gfe),gzip(gfe)
X-HTTP-Via: 1.1 localhost:5656 (squid/2.5.STABLE11)
Complaints-To: groups-abuse@google.com
Injection-Info: r35g2000prh.googlegroups.com; posting-host=195.154.188.107;
   posting-account=XWbNBg0AAABXMdLVqoM3R9n-RIU90_cb
Xref: news.f.de.plusline.net comp.databases.theory:43519

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