[thelist] Message Archive - MySQL

[Simon Willison]

Hi Chris,

Wednesday, July 30, 2003, 3:47:20 PM, you wrote:
> However, although the client seems sure that no one will *ever* want to
> recover a message more than 30 days old, I do not share his faith.
> Consequently, what I want to do is to create a table with the same
> structure as 'message' called 'message_archive'. Instead of immediately
> deleting, I want to find each pertinent record, insert the data into
> 'message_archive', then delete the record from 'message (as above). The
> application is ASP (VBScript), and the database is MySQL. Can anyone
> assist or at least prod me in the right direction?

A better way of doing this may be to have a 'flag' in the message
table called 'deleted', which can be set to true or false. You can
then add a clause to all of your select statements saying "where
deleted = 'false'" and everything will work just fine. This saves you
from duplicating your table structure, which is a bad idea as it means
more work should you change the layout of the table. If you put an
index on the deleted field the performance overhead of the new column
will be so minimal as to be inconsequential (even without an index
you'd be unlikely to notice the difference). If you ever decided you
/really/ want to delete stuff you can always run "delete from messages
where deleted = 'true'".

Hope that helps,

Simon


-- 
http://simon.incutio.com/