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….
It's All A Bet - MLOps, DevOps, AI and Synthetic Data Design 
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.”
Love it, practical as anything 😀
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.
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.
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.
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.