Create recycle bin table

Asked

Viewed 90 times

2

I have the database tables to store the data, but I want to add one to save the data of deleted rows from the other tables, I thought of four ways to do this:

  1. Create a table lixeira that will save the table name and the primary key of the deleted line (plus other information like date, user who deleted, etc), in the other tables would add a field deletado boolean type, so instead of deleting the line only changes this value (besides registering a row in the table lixeira)

  2. Create a table lixeira that will only save the information as date, user who deleted, etc, in the other tables would add a field deletado which would be a foreign key to the table lixeira, so instead of deleting the row only changes this value (in addition to registering a row in the table lixeira)

  3. Create a table lixeira which will save the information as date, user who deleted, etc and the deleted row data formatted as JSON, excluding the row from the original table

  4. Create a recycle bin table for each bank table, for example, usuario_lixeira, produto_lixeira, etc, and, before deleting the data in the original table, add them in your recycle bin table

In general, what would be the best alternative? What problems will I have with using method A or B? If you need to eventually search for an deleted row, can any method make this very complicated? Keeping data deleted in the original table (method 1 and 2) will reduce query performance * ? Indexing can solve this last problem?

* Considering a table that has many records and that are excluded with relative frequency

Other ways to achieve the same goal are welcome,

mysql sql modeling

  • Did the answer resolve what was in doubt? Do you need something else to be improved? Do you think it is possible to accept it now?

1 answer

2

What’s best is you only know because only you are making the case. I don’t know if it’s because people don’t understand that, or if they’re really stubborn, or if they think that I and other people, like Bacco for example, just talk nonsense. Because all the time we talk about these things and everyone thinks there’s a magic answer that fits every case. It doesn’t exist. It takes a lot more information to make the decision. And the current description has moment that gives the impression of being on one table, on another on a few or many, or all, what changes the decision.

And you don’t have information because you need these tables, so I don’t know if 4 is useful or 3 is better. I don’t really know if I would use JSON. And I could use another way, the fact is that 3 and 4 delete the original and this seems to me more important decision. It looks like it might be one of those two, but I’m actually afraid to say it, because I might be missing information. I don’t know if it makes sense to keep the original or not.

If you need to keep the data on the table for some reason, and you may be right, there are those who say that nothing should be effectively deleted (which is still just a cake recipe, so I’m against it if you say so), then it seems to me that the best option is:

  1. Leaves everything in the original table and controls the audit on it even having optional columns and appropriate indexes considered the deleted rows. There’s no auxiliary table, if it doesn’t create a problem I don’t know.

I see little reason in the current databases to use 1 or 2. Then who will choose it would need to justify why you need to do this, I see no reason. Save space? You will spend more (if all this is confirmed). The details of how to do this can be as or more important. But keeping a lot of things deleted on the same table may not be good. The index is certainly important, but if it was much deletion even the index can be impaired, but it is little, index has a logarithmic deterioration, so it is not a big problem, the physical limit can be worse, and it matters also.

Trying to infer from the writing I would guess that 3 or 4 would be better depending on the number of tables and the need to access these deleted data frequently. Some things make me believe that you do not need to keep the original deleted data, but this may already be a conceptual error, so I do not affirm 100% if that is it. I could do something like:

  1. Create a table lixeira that will guard tabela, linha, coluna and valor, including her unique columns that do not see from the original.

Or I would do 3, but in simpler format than JSON, but I could use pure 3. Or I wouldn’t do any of this because it’s another problem. I’m not ruling out 4, it has merit in certain scenarios.

And there may be other challenges that are not covered there.

  • That is why the "in general", that is, in the most common cases as a small system for an uncle’s commerce. The idea of the same question was to find possible flaws and problems that would find in using one of them. Thank you for the two other forms. But what format would you use instead of JSON? In the case of 6, would you have a row for each column of each deleted row? And how would the other data (who deleted, date, etc)?

  • You’d have special codes for them. It has an advantage when amendment also uses this mechanism, which should, after all, audit mechanism that only does with deleted ones does not seem reasonable. That’s why I said there must be other flaws in the idea. The format depends on the need.

  • I found very good the answer, I could add the comment regarding option 6, which has advantage when used in changes as well. A rationale for using option 1 or 2 is that a data cannot be deleted, for example, in a stock system, if the trader no longer wants to work with a product, he will delete it, but this can cause serious errors in a future report. May appear expenses/gains that do not match and even break the application, however it is a simple system does not need to save all the changes made, suddenly nor need the date and user that deleted ...

  • @Guilhermecostamilam I don’t know if I understand what you want...

  • ... A boolean may be enough, but it may also be that you have more than one administrator of these products and it is useful to know who deleted what. In cases like OLX, a user can delete their ads, but suddenly the company needs to pull weekly and monthly reports of the reasons that led users to delete ads to improve the tool, make this search in case 3 with JSON format or other can be complicated right? And the fact that you have hundreds of ads created and deleted daily (probably more), keeping them in the original table could be a problem? ...

  • Which option to use? In this case, they might want to keep the change history, and then use option 6, but how to ensure data integrity? Cannot use a FK that uses 3 columns to reference a PK, or has as?

  • Your answer is good, but as you can see I still have some gaps open, this doubt not for a specific system, but would use base to decide in future what I will do

  • There you are giving another context, and even your comment shows that there is no clear requirement, so neither I nor you can answer the want is better. You can use a FK any way you want. Your problem is just having gaps, you still don’t know what your problem is, so there’s no way to know what the solution is. First you have to decide what you need, then choose an option.

Show 3 more comments

Browser other questions tagged mysql sql modeling

You are not signed in. Login or sign up in order to post.