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 '0000-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]
