keeping track of "deleted" rows

[dadamssg87]

Right now, my users can delete pieces of content that they've created and the rows really do get deleted from the database. I'd like to change this by adding a "deleted" column in my database table. The column datatype needs to hold the date/time of deletion. So i'm thinking datetime, timestamp, or int(11). I'm just not sure which datatype is ideal. I was just going to set the datatype to int(11) to store the integer value of the timestamp of when the row was "deleted". Then i could just query non-deleted items by "select * from table where deleted != '0'". But i think i should be using datetime or timestamp. I don't really understand the difference between the two.

 

What do the mysql wizards suggest?

[Muddy_Funster]

I dunno about the wizards, but I would use a timestamp field with default value of NULL and no on update action.

Update it it with CURRENT_TIMESTAMP() when something is deleted, then select the info by NOT using * with the WHERE <timestamp_filed> IS NOT NULL.

 

Using a date time formated field lets you report against the filed (as I am assuming you want to do or else you would just be using a flag field) without having to convert values to get the ranges that you are looking for.

[fenway]

Using NULLs with date ranges is annoying, but you can store all zeroes.