need database advise: deleted record

Anon

I need an advise on idea which way to take. I have a table. User can delete record in the table but I want to keep these record so it can be undo later if need (like Recycle Bin). There are two ideas: add one field 'delete' = (y,n) in that table, OR add another table and throw those deleted records in that table. The first method is very simple, however, my question is: Is it gonna slow down the database to process a sql query if the database size is large (due to too many deleted record not inuse)? Do those deleted records effect anything? The second method is too complicated because involving moving a massy amount of data (there is some field in the table are MEMO with size up to 2M😎. Please help.

Anon

I would go with the second method.

I don't agree that the second method is difficult. All you need is to create the table. Then whenever someone does a DELETE, you just issue two queries:

INSERT INTO tbl2 (SELECT whatever FROM tbl1 where ...) VALUES(..., ..., ...,) WHERE ...
DELETE FROM tbl1 ...

And u're done!

If you go with the first idea, you'll get a table that is filled with data that may never be used again, and that is surely gonna slow down your queries on that table.

Richie.
geocities.com/akindele/

Anon

I'd use the first method (add the "delete" column). With a good index or two on the table the response time won't change at all with the extra records.

Anon

You may be right tombath. But think what is gonna happen to the table, long term.

I have a classified ads site, where ads expire after 45 days. Assuming I don't move the old records to another table, or delete them, the table is gonna grow too big. Then what?