Discuss
DELETE FROM `table` or UPDATE `table` SET `deleted` = 1
Moderator: General Moderators
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
DELETE FROM `table` or UPDATE `table` SET `deleted` = 1
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
Discuss
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.
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.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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.
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.
- Ollie Saunders
- DevNet Master
- Posts: 3179
- Joined: Tue May 24, 2005 6:01 pm
- Location: UK
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.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.
This is just a half-baked thought, but what if you combined the two...
you probably wouldn't need the NOT NULL but it illustrates my point
Code: Select all
DELETE FROM products WHERE deleted NOT NULL AND deleted > {timestamp}- dibyendrah
- Forum Contributor
- Posts: 491
- Joined: Wed Oct 19, 2005 5:14 am
- Location: Nepal
- Contact: