...

View Full Version : really DELETE or set deleted='1'?



guvenck
06-08-2007, 01:14 PM
Hello,

I need some advice from experienced users. Let's say I have such a DB structure where I keep my pages' information:

TABLE pages:
ID | html | active | deleted

I give the admin to delete the pages from the DB. Till yet, I did not actually delete the pages from the DB using



$result = mysql_query("DELETE FROM pages WHERE ID='$pID' LIMIT 1") or die(mysql_error());


but



$result = mysql_query("UPDATE pages SET deleted='1' WHERE ID='$pID' AND deleted='0'") or die(mysql_error());


So, I actually did not delete the pagerows but make them look like they are deleted. Naturally, with every live mysql_query I need to include the deleted='0' to work with undeleted pages.

I wonder, if you did the same, are there negative consequences of this? I am thinking of really deleting the pages but fear that the DB will recreate the ID's in the future when new pages are created and the old ID's are not there anymore. The ID field is set to auto_increment.

firepages
06-08-2007, 01:18 PM
depends :)
I normally delete unless the table is linked to others, e.g. imagine a 'suppliers' table, and imagine that at some point that supplier goes broke or you just stop using them... IF e.g. your products database is linked to your suppliers database and you want to keep historical data you have little choice except keep the data, I normally have an 'active' field which will stop the record ever turning up in selects, yes it is an extra index but the alternative is keeping historical data in flatfiles or duplicate databases.

guvenck
06-08-2007, 01:28 PM
That was a quick reply! My table is linked to others, but this is a table where I keep the permissions about which profiles can access this page. After deleting the page I can delete the rows of permissions that are related to this page. What kind of "historical" data can I keep for example? Page visits?

I also fear that MySQL will recreate the ID's that are deleted. Let's say I have deleted page with ID=1 and sometime when I create a new page in the future, can it recreate a page with ID=1 again? That won't be good.

daemonkin
06-08-2007, 02:56 PM
If you are using auto increment and delete a record MYSQL will not assign a new record with a previously used ID. I think. You can even set what number the records will begin at.

eg: I use the IDs for my ordering system and set the base record to be 1,000. All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!

Hope this helps.

D.

guvenck
06-08-2007, 05:08 PM
I am using autoincrement and till yet, the number to start was naturally 1 as an autoincrement value was not set.



eg: I use the IDs for my ordering system and set the base record to be 1,000. All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!


Thanks for the tip :)

Fumigator
06-08-2007, 07:06 PM
Auto-increment won't re-use the same number because it keeps track of which number it used last, not which numbers are currently available. So don't worry about that.

If you are waffling on the issue of delete vs. update the deleted flag, then you may prefer a compromise: Move the row (via delete and insert) into an archive table. That way your primary table stays clean, but you have the old data to refer to if it's needed.


All other records will increment from 1,000, inflating the site by not looking amateurish and beginning with order #1!

I do the same thing, but I do it to keep the ID length consistant, which means I start with 100000 (or even 1000000 if I think there will be more than a million records some day). It's a silly thing to do, but I prefer it, and it's my system, so I can do what I want! :p

GSimpson
06-09-2007, 12:04 PM
just acting on the first question, you could output the code to a new html file in the a folder named "backups" then delete it from database. Just grab it from backups when you have too many backups. Keeps mysql space and webspace down. If that's what you asked, I didn't understand the question completely. :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum