Page 1 of 1
DELETE FROM `table` or UPDATE `table` SET `deleted` = 1
Posted: Wed Mar 28, 2007 10:36 am
by Ollie Saunders
I've heard people say you shouldn't ever DELETE from a table and should instead have an indexed `deleted` field that you can set to true or false. Is this really a good idea?
Discuss

Posted: Wed Mar 28, 2007 11:23 am
by Luke
I do that for a lot of things... some things I just delete. I like using that method because the applications I build are for users who are sort of "n00bs", so a lot of time they come to me and say "I JUST DELETED EVERY USER FROM THE DATABASE!!! WHAT DO I DO?" It's nice to tell them that I can log in and reset their changes.
For certain things I think it's kind of pointless though.
EDIT: The downside is that you have to remember to do "WHERE DELETED != 1" in all of your queries.
Posted: Wed Mar 28, 2007 11:33 am
by Ollie Saunders
The downside is that you have to remember to do "WHERE DELETED != 1" in all of your queries.
Yeah I can probably cope with that. Can anyone comment on performance though?
Posted: Wed Mar 28, 2007 11:50 am
by Begby
I am not aware of performance issues, you just need to add deleted to your indexes. Of course there will be an issue though if you have 10 million records marked as deleted and only 2,000 active records.
I usually only use this method when marking items that need to be referenced again in the future, for instance deleting products when they need to still be listed in an order history. I don't see any other reason to keep records around if they will never again be used.
Posted: Wed Mar 28, 2007 11:53 am
by Ollie Saunders
I usually only use this method when marking items that need to be referenced again in the future, for instance deleting products when they need to still be listed in an order history. I don't see any other reason to keep records around if they will never again be used.
Hmm yes I agree with that. Perhaps the best thing would be to have a cron that DELETEs the deleted = 1 records after x days but I guess then you need a dateDeleted record and it starts to sound like work.
Posted: Wed Mar 28, 2007 12:48 pm
by Luke
This is just a half-baked thought, but what if you combined the two...
Code: Select all
DELETE FROM products WHERE deleted NOT NULL AND deleted > {timestamp}
you probably wouldn't need the NOT NULL but it illustrates my point
Posted: Thu Mar 29, 2007 1:07 am
by dibyendrah
I think this idea will not be useful for the database with relational tables. Is it right ?