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

11

I have the following question:

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

In case you would like a good practice tip, on which tables does I make a STATUS column? If the tables have this STATUS column 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, CUSTOMER, CONTRIBUTOR, SUPPLIER, ORDERING, ORDER OF SERVICE, ORDER FOR SALE, PRODUCT, SERVICE.

    
asked by anonymous 27.10.2015 / 23:14

2 answers

8

To complement the theme, I would like to point out some reasons why you should decide to remove the data from the database instead of disabling the 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 maintain historical tables or even rely on backup.
  • Avoiding one more level of complexity in relationships, as it is an additional cost to write a clause to test the state of the record in each query or join, otherwise you forget to do so. Also, if the idea is not to allow data loss, changes to the database should also always generate new records . Some scenarios that deal with historical data of this nature are very 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 role to permanently purge sensitive information, or even because he has the right to do so. It is not always a feature 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 a few times 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, although it is ideal to always maintain the entire history of data, this is not always feasible. Even when some implementation is done via the system, it is always good to have a reliable backup system.

    
28.10.2015 / 01:55
7

Removing Data is not a Recommended 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 go without 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 since a customer's UltimaOrder (a simple optimization) may point to a Purchase Order that has been logically removed.

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

Let no one let him do this.

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

Purchase orders are not removed - they are canceled. There may even be a fee 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 should focus on the task that 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 in all cases is the creation of a field that allows to know in what state the current information is, so no data will ever be lost, allowing in the future to be analyzed for some possible decision making. >

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

    
27.10.2015 / 23:28