When should I inactivate or delete a record? Good database practices

Question

I have the following doubt:

When should I inactivate a record? When should I delete a record?

In case you would like a good practice tip, in which tables it pays to create a STATUS column? Being the tables that have this column STATUS I will do an UPDATE to change the situation to active or inactive and those that do not have this column I will use DELETE to delete.

Tables containing my mysql database: USER, CLIENT, COLLABORATOR, SUPPLIER, BUDGET, ORDER OF SERVICE, ORDER OF SALE, PRODUCT, SERVICE.

Answer 1

Removing Data is not a Best Practice

Removing a line or entity is rarely simple. The operation affects not only the model data but also its form. This is why we use foreign keys, to ensure that the items of an Ordemdecompra are not without an Ordemdecompra associated. And this is the simplest case. ...

When we work with logical removals, it’s easy to get to situations where we have corrupted data since a Client’s Ultimaorder (a simple optimization) may point to an Ordemdecompre that has been logically removed.

Let’s say our marketing department decides to remove a Product from the catalog. Should we then remove all Purchase Orders that contain this Product? Should we also remove all Invoices related to these orders? Going further, we must recalculate the company’s profits?

Let no one let him do it.

In fact, 'removing' a Product means that it will be discontinued. We no longer want to sell that Product line. We want to get rid of the stock we have and never buy from our supplier again. The product should not appear in user surveys, but the warehouse staff still have to manage these items. Anyway it’s much easier to just say 'remove'.

Purchase orders are not removed - they are cancelled. There may even be an incidence of some fee if the order is cancelled too late.

Employees are not removed - they are fired (or retire). Termination can also be handled in the system.

Vacancies are not removed - they are filled.

In all cases we should focus on the task that the user wants to perform instead of the technical action that should be executed in an entity. In almost all situations, more than one entity will be taken into account.

The most correct in all cases is the creation of a field that allows to know in what state the current information is, so it will never be lost any data, allowing in a future to be analyzed for some possible decision making.

Data is of extreme value in any business, especially those related to IT, always keep them stored.

Answer 2

To complement the theme, I would like to point out some reasons for deciding to remove the data from the database instead of disabling the respective records:

• A system where historical data would represent an unacceptable volume in terms of space or performance and therefore need to be purged from the table. An alternative in this case is to keep historical tables or even rely on backup.
• Avoid a further level of complexity in relationships, as it is an additional cost to write in each query or join a clause to test the state of the record, apart from the risk of forgetting to do so. Also, if the idea is not to allow data loss, changes in the database should also always generate new records. Some scenarios that deal with historical data of this nature are quite complex, such as when you need to query records and their relationships in different moments.
• Security and privacy often play an important role on this issue. In many cases the user needs to have a function to definitely purge sensitive information, or even because he has the right to do so. It is not always a functionality made available directly to the user, but many companies inevitably have to implement some mechanism to deal with this situation. Here in the OS even sometimes users post data as passwords or private keys and developers need to delete the information, something that is not available to normal users or moderators.

In conclusion, while it would be ideal to always keep the entire data history, this is not always feasible. Even when some implementation is done via system, it is always good to have a reliable backups system.