|Best Practice for record deletion|
Hi all, looking for advice on the best (most proper) way to delete something from a db.
Have a fairly complex set pf tables, with one of the base tables being a user table. Things such as bookmarks, account activity, posts, etc are tied in from different tables via a foreign key dependancy via MySQL INNODB. I have these 'sub' tables set up to cascade on delete, such that, say, through phpmyadmin, when I delete a user, all data tied to their account is cascade removed from the db.
Now, I am finishing off a user-initiated deletion routine, and am wondering about best practices via PHP. Should I manually visit each table via PHP and delete any user data associated with a given user, or should the script simply rely on the db and delete from just the user table, allowing the db to handle the cascade?
Pros and cons on both sides I guess... say the relation is lost, for example on the db side (although this would indicate a larger db failure...). I am leaning towards putting the onus on the db here, any comments, suggestions?
I'd say the script should rely on the database. Once your main user record is deleted, it should be impossible for that user to access any of the related records anyhow (since most of your scripts probably depend upon finding a valid user record first).
The only risk here is if whatever mechanism you have setup to delete related records from other tables is delayed in some way. (Like it's a cron job that only runs every xx minutes.) In this situation, you might have some of the activity from a deleted user that still shows up in other areas (for example, if you have a page of recent activity of all users) yet doesn't have the relevant user data to have it appear correctly.
Testing for these cases would be important regardless of your deletion mechanism.