How to implement temporary data in MySQL

Question

I currently have a temporary MySQL DB and you need to change it to a temporary MySQL database. In other words, I need to be able to keep a record of the changes that have been made to the record over time for reporting purposes.

My first thought for implementing this was to simply insert into tables instead of updates, and when I need to select data, just do GROUP BY in some column and sorting by DESC timestamp.

However, with a little thought of things, I realized that it would really hurt, because the primary key for each insert (which would just simulate several updates on one record) would be different and thus the mess of any connection that uses the primary key to communication with other entries in the database.

Thus, my next thought was to continue updating the main tables in the database, but also create a new insert in the “audit table”, which is just a copy of the full record after the update, and then when I needed to report temporary data I could use an audit table for queries.

Can someone please give me some tips or links on how to do this correctly?
Thanks.

Answer 1

Make this table R temporary (i.e. maintain history).

One project is to leave the R table as it is and create a new R_Hist table with valid_start_time and valid_end_time. Actual time is the time when the fact is true.

CRUD operations can be defined as:

INSERT

• Paste in both R
• Paste in R_Hist with valid_end_time as infinity

UPDATE

• Update in R
• Paste in R_Hist with valid_end_time as infinity
• Update valid_end_time with current time for the "last" tuple

DELETE

• Remove from R
• Update valid_end_time with current time for the "last" tuple

SELECT

• Choose from R for “snapshots” (implicitly “last timestamp”)
• Choose from R_Hist for temporary operations

Instead, you can select a new table for each attribute of table R. Thanks to this particular design, you can capture temporary data of the attribute level as opposed to the entity level in the previous project. CRUD operations are almost similar.

Answer 2

I made the column deleted and the DeletedDate column. Deleted by default false and the deleted date is empty.

Complex primary key for IDColumn, Deleted and DeletedDate.

You can index by remote so that you have very fast queries.

There is no duplicate primary key in your IDColumn, because your primary key contains deleted and deleted dates.

Assumption: you will not record more than one millisecond in the same record. May cause a recurring primary key problem if the deleted date is not unique.

Then I make a transaction with the type of transaction for updates: I select a row, get results, update specific values, then insert. Indeed, this is updating the deleted truly remote date to now (), after which you post the line after the update and use it to get the primary key and / or any values ​​that are not available for any API that you created.

Not as good as a temporary table and requires some discipline, but it combines history into 1 table, which is easy to report on.

I can start updating the deleted date column and change it to added / deleted in addition to the added date so that I can sort the records by 1 column, added / deleted column, while always updating the AddedBy column and just setting the same value as the added ./ Removed column for registration for the sake of.

In any case, you can simply perform the difficult case when the null value is not null, since addDate is still added in the order of adding the added date, as added by the added date. so yes, whatever, it works.