When should I inactivate or delete a record? Database Best Practices

Question:

I have the following question:

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

In case I would like a good practice tip, in which tables is it worthwhile to create a STATUS column? As the tables have this column STATUS I will make 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, SERVICE ORDER, SALE ORDER, PRODUCT, SERVICE.

Answer:

Removing Data is not a Best Practice

Removing a line or an entity is rarely simple. The operation affects not only the model data but also its shape. That's why we use foreign keys, to ensure that the items in a Purchase Order do not have an associated Purchase Order. And this is the simplest case. …

When working with logical removals, it is easy to get into situations where we have corrupted data as a Customer's LastOrder (a simple optimization) may point to a PurchaseOrder that was 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 that Product? Should we also remove all Invoices related to these orders? Going further, should we recalculate company profits?

Let no one let him do this.

In effect, 'remove'' a Product means it will be discontinued. We no longer want to sell this product line. We want to get rid of the stock we have and never buy from our supplier again. The product shouldn't appear in user searches, but warehouse personnel 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 a charge if the order is canceled too late.

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

Jobs are not removed – they are filled.

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

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

Data is of extreme value in any business, especially IT related, always keep it safe.

Scroll to Top