---

title: “The handiest single bit of information a php/java/ruby/[insert your lang here] will ever need to know about database design.”

date: 2011-02-21

Don’t trash out your data… just don’t do it.  Don’t physically delete it as it’s a real pain in the arse to get back.

Have a deleteflag instead… 

CREATE TABLE users (

id int(11) not null primary key auto_increment,

firstname varchar(100) not null default “”,

lastname varchar(100) not null default “”,

email varchar(100) not null default “”,

createdate int(11) not null default -1,

lastupdate int(11) not null default -1,

deleteflag int(11) not null default -1

);

Deleting data then becomes a cinch:

UPDATE users SET deleteflag=UNIX_TIMESTAMP() WHERE id=[id_of_the_deleted];

It makes life a heck of lot easier when a customer phones you up saying they’ve deleted all their data and is there a quick way of getting it back.

Yup, it’s easy.

UPDATE users SET deleteflag=-1 WHERE id=[id_of_the_deleted];

There, data saved for a client’s emergency.  You’ll all look great and I don’t want a royalty….