Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Any thoughts/ideas ? There are pros and cons to both ways.(As you pointed out with moving the records to another table) I allways prefer flagging the records. The draw back with flagging the records is that you might sacrifice some speed(depends on the number of records in the table.) If the table does not grow that fast most def just flag the records as deleted. my2c worth -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a different table. However if we just flag the record as deleted the restoring is quite easy. Sorry I don't understud this, why it's impossible ? If the PK is auto-generated from MySQL it will have progressive numbers, and it's always possible to force a lower, non-existant number in the PK. Any thoughts/ideas ? We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Bastian Balthazar Bux wrote: We need to track the modification to the records too so the route has been to keep them all in a different, specular databases. If the real table look like this: CREATE TABLE `users` ( `id` int(11) NOT NULL auto_increment, `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`id`) ); The backup one look like this: CREATE TABLE `users` ( `del__id` int(11) NOT NULL auto_increment, `del__ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `del__flag` char(1) default 'D', `del__note` mediumtext, `id` int(11) NOT NULL auto_increment, `ts` datetime NOT NULL default '-00-00 00:00:00', `username` varchar(32) default NULL, `password` varchar(32) default NULL, PRIMARY KEY (`del__id`) ); That is the first one whit del__* fields added but all indexed removed. Having the same name and similar schema for the two tables make easier have a photo of the database in a defined time slice. Usefull with small, not very often changing databases. hi bastian just a thought. rather stay away from auto_increment PK's and rather generate your own PK. i have run into trouble a couple of times using auto_increment when i made backups and restored the data again. The PK changed and i had records in other tables referencing the old PK , but then the PK changed. -- Arno Coetzee Developer Flash Media Group Office : 2712 342 7595 Mobile : 2782 693 6180 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best practices for deleting and restoring records - moving vs flagging
Saqib Ali wrote: Hello All, What are best practices for deleting records in a DB. We need the ability to restore the records. Two obvious choices are: 1) Flag them deleted or undeleted 2) Move the deleted records to seperate table for deleted records. The first is what I like more. While in the first to mark as deleted (or restore), you only have to change one column, and in the second, you have to move (and move again to restore) from one table to another. Getting the value from the normal value to store it in the second could lead to a problem Implement the first in a developed schema, is just add a column of type bool (for example) with the default beeing not deleted. The second has the other problem of a change in the schema of the normal table has to be done in the deleted values table. We have a complex schema. However the the records that need to be deleted and restored reside in 2 different tables (Table1 and Table2). This lets you to have two different tables of deleted values. Table2 uses the primary key of the Table1 as the Foriegn key. The Primary key for Table1 is auto-generated. This make the restoring with the same primary key impossible, if we move deleted data to a If you mean The Primary key for Table1 is auto-generated by using auto_increment, it is not impossible. You can just copy the entire contents of the row. different table. However if we just flag the record as deleted the restoring is quite easy. As I said. Any thoughts/ideas ? Just my opinion, and it seems to be the opinion of mambo developers, as they implement the deletion of values to restore like this way, and they have also a published column. If they have done this they would need 4 tables: published_and_not_deleted, published_and_deleted, not_published_and_not_deleted and not_published_and_deleted. I would say that the second is very bad. -- Nuno Pereira -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
