Re: Storing "deleted" data

<zeldorblat_at_gmail.com> wrote in message news:1130700808.833451.143900_at_g43g2000cwa.googlegroups.com...
> We are looking for a way to represent and store "deleted" or old data
> in our database. The idea is that we have some records for active
> customers/people/whatever and some for those that we no longer care
> about. We can't just delete those rows, however, because it would
> violate referential integrity. But we would like to have a way to
> exclude them so they don't clutter drop-down lists, reports, etc.
>
> I've come up with two options so far:

> 1. Just add a bit column to the table -- 'isDeleted' or something
> 2. Create two separate tables -- one with current data and one with old
> data

> #1 is the easiest to implement and preserves referential integrity.
> But the queries must now discriminate on a column with only two
> possible values (i.e. an index on the isDeleted column won't really
> help).

Have you done a test ?

> #2 Isn't too bad -- the table containing current data is smaller and
> faster to update/select. But in this case I don't know how to define
> the integrity constraints. As far as I know you can't have a foreign
> key reference to one table OR another.

a)You can split all tables in current/old. b)You can try to declare a foreign key reference to one table AND another. Received on Mon Oct 31 2005 - 10:05:03 CET