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

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… 


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….



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

  1. You might want a "deleted_date" instead of a simple flag, so you know how stale the data is and so how "safe" it might be to delete it.

  2. John, I’ve seen some folk do a deleted=0/1 type scenario. I always go for a timestamp so I can recall the date. It’s also handy to find users who’ve deleted in the last six months etc.

  3. Another, more complex option, is to use trigger to populate a users_deleted table with the deleted data, that way your table stays clean, but the data is safe.

  4. Simon, not a bad idea at all. I personally like to keep things as clean as possible so I can also maintain control from a class level. Handy methods like public boolean isDeleted(){ if(this.deleteflag > -1) return true;} Once you get pivot type tables into the mix it starts to create work.All good fun though and thanks for the comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: