Re: Newbie question on table design.

I recommend the KISS approach. Disk space is cheap. Development isn't.
Any time you split a relation horizontally or vertically, the constraints
needed to maintain integrity change--sometimes in ways that are not
supported by the DBMS. For example, splitting a relation horizontally
requires the addition of a circular inclusion dependency. Splitting a
relation vertically complicates the definition of key constraints and can
really complicate interrelational dependencies. Furthermore, depending on
the DBMS and the other columns in the table, the addition of a bit column
doesn't add any space at all. In SQL Server, for instance, 2 bytes are
allocated per row whether you have 1 bit column or 16.

Another solution would be to leave the rows intact in the original table and
add an additional table that contains only the key columns from the original
table for the deleted rows. All that would be needed to maintain integrity
would be a simple foreign key constraint. You could then create a view that
would exclude any row from the original table that also has a row in the
other to simplify the queries that require is_deleted to be 0.

<somedeveloper@xxxxxxxxx> wrote in message
news:1177566521.400997.267690@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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: OO vs. RDB challenge
    ... in practice its outweighed by the unfortunate fact that SQL ... > The Relational Model is the best possible layer, ... constraints of the RDBMS" is an attractive idea in theory, ... RDBMS in some other language and show its as easy as using an RDBMS". ...
    (comp.object)
  • Re: not using foreign keys?
    ... front-ends are technically incapable of enforcing referential integrity. ... and current integrity constraints. ... Trouble was the Vehicle data was needed to reprint the invoices. ... Actually, I have the worst, some constraints in the app, some in the ...
    (comp.databases.oracle.misc)
  • Re: loop and recordset
    ... those members whose membership lapsed. ... Use the SQL language to return only the set of required ... This table obviously needs some constraints. ... ALTER TABLE enrollment_2 ADD ...
    (microsoft.public.access.modulesdaovba)
  • Re: loop and recordset
    ... there IS a missing period? ... Could your SQL approach be modified to deal with such a recordset? ... This table obviously needs some constraints. ... ALTER TABLE enrollment_2 ADD ...
    (microsoft.public.access.modulesdaovba)
  • Re: Help: Stored Procedure performance issue.
    ... Please post DDL, so that people do not have to guess what the keys, ... We do not use flags in SQL. ... are no more than n days apart) and other constraints are met (the cargo ...
    (comp.databases.ms-sqlserver)