znf96 znf96 - 4 months ago 11
SQL Question

To hide the no longer used record instead of deleting it permanently by using PHP and SQL

I'm doing a food delivery system for my final year project. For the database, I'm required to hide the record that is no longer in used, instead of deleting the record permanently. For example, if the seller doesn't want to sell a particular meal, they can disable the meal but the record of the meal still available in the database. I need to achieve this by using PHP and SQL. Can someone give me some ideas on how to achieve this? Thanks in advance.

Answer

The feature you are referring to is something called soft deletion. In soft deletion, a record is logically removed from the database table, without actually removing the record itself.

One common way to implement soft deletion is to add a column which keeps track of whether a column has been soft deleted. You can use the TINYINT(1) type for this purpose.

Your table creation statement would look something like this:

CREATE TABLE yourTable (`sd` TINYINT(1), `col1` varchar, ...)

To query out records which have not been logically deleted, you could use:

SELECT *
FROM yourTable
WHERE sd <> 1

And having a soft delete column also makes it easy to remove stale records if the time comes to do that.