homepage Welcome to WebmasterWorld Guest from 50.17.86.12
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Best Practice for record deletion
mysql,innodb,php,deletion
pmmenneg

5+ Year Member



 
Msg#: 4089612 posted 12:59 am on Mar 2, 2010 (gmt 0)

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?

Thanks!

 

whoisgregg

WebmasterWorld Senior Member whoisgregg us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4089612 posted 9:43 pm on Mar 23, 2010 (gmt 0)

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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved