The table has following fields: id, date, user_id, city, price
I would like the rows that are older than 1 year to be automaticaly deleted, but I am not really sure about how to do this. Should it be done by application or database? How?
Another issue here is that there are going to be ~50k inserts every year. What will happen to 'id' field in a couple of years? Won't the number get too large? What could be done in this case?
Many thanks in advance.
I would suggest creating a basic shell that handles deleting the records, and schedule a cron job that runs whenever you wish to check for records that should be deleted. The shell could be very simple, something like this:
class JanitorShell extends AppShell {
// put the model you wish to delete from here
public $uses = array('Model');
public function main() {
$this->Model->deleteAll(array(
'Model.created <' => date('Y-m-d', '-1 year')
));
$this->out('Records deleted.');
}
}
Then your cron job would run your shell. To run your shell, call:
cake -app /path/to/app janitor
This assumes cake is in your PATH. Of course, this is a very basic shell and could be easily expanded, keeping logs of what's been deleted or even just moving deleted records to a new table, a sort of 'soft delete'. You should probably not put such destructive code in main() since it runs each time you run the shell, but this will get you started.
To answer your second question, 50k inserts a year is nothing to fret about. To know the limits of your primary keys, read up on MySQL datatypes.
actually I think it should be
date('Y-m-d', strtotime('-1 year'))
