[Techtalk] Data archiving]

[Christine Cage]

Michelle,

Sorry for the late comment here, but another few cents of wisdom:

I empathetically shudder with you at the thought of deleting data.  There 
are cases where it is appropriate, but you haven't written anything below 
that suggests this is one of them.  I'm reading a data modeling book right 
now that explains in THE FIRST THREE PAGES the reality of "forgotten data", 
i.e. stuff that the customer said to throw away but asks for a few months 
later.

But more pragmatically, the issue you describe seems to be symptomatic of 
two deeper issues:

   1. Removing data from the database to address display issues is throwing 
the baby out with the bath water -- change the query, not the data.

   2. A potential problem with the database schema in use not knowing when 
rows are created.

You should have a column on each table that timestamps when the row was 
created.  If you do, then just change the SELECT query to check it.   
Something like:

tbl_mstr
tbl_id
tbl_row1
tbl_row2
tbl_crt_by
tbl_crt_dt  (date default  'Now()'  NOT NULL)
tbl_mod_by
tbl_mod_dt (timestamp NOT NULL)

SELECT tbl_id, tbl_row1, tbl_row2
FROM tbl_mstr
WHERE tbl_crt_dt < $stale_dt;

Of course, if the database doesn't have these columns this won't work.  An 
after-the-fact solution is to add a new one-bit column that toggles from 1 
to 0 indicating staleness and which can be used to prevent display.

Good luck,
Christine Cage-Newton
http://www.craigslist.org/sfo/sfc/cps/7137575.html


> > ----- Original Message -----
> > From: "Michelle Murrain" <tech at murrain.net>
> > To: <techtalk at linuxchix.org>
> > Sent: Saturday, November 30, 2002 12:31 PM
> > Subject: [Techtalk] Data archiving
> >
> >
> >> I've been writing web-based database stuff for a while - but it's all
> >> been in the "small" scale of databases - in the small hundreds of
> >> records or less.
> >>
> >> Well, one particular installation is getting bigger, and the way that
> >> I've written the application, it's getting very unwieldy for them to
> >> deal with the output of tables, and they don't want to see anything
> >> more than 1 month old. They want to delete this stuff, but I shudder
> >> to think of that.
> >>
> >> So, I'm thinking of 3 different strategies for archiving:
> >>
> >> 1) Take the old stuff out of the database and stick it in a delimited
> >> text file so they can get it later, if they want.
> >> 2) Take the old stuff and toss it in an "archive" table within the
> >> same database
> >> 3) leave it in the table, and provide an easier front-end to choose
> >> the range of data they look at, and perhaps choose a default or
> >> something.
> >>
> >> I'm leaning toward either 1 or 3, I might actually implement both.
> >> But any suggestions in terms of performance as well as overall db
> >> design for people who've done this sort of thing would be helpful.
> >>
> >> Thanks.
> >> --
> >> .Michelle
> >>
> >> --------------------------
> >> Michelle Murrain, Technology Consulting
> >> tech at murrain.net     http://www.murrain.net
> >> 413-253-2874 ph
> >> 413-222-6350 cell
> >> 413-825-0288 fax
> >> AIM:pearlbear0 Y!:pearlbear9 ICQ:129250575


_________________________________________________________________
Add photos to your e-mail with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail