How to manage version control data stored in mysql

Question

I am trying to use a simple mysql database, but I am setting it up so that each field is backed up by an unlimited number of versions. The best way I can illustrate this is by replacing each field of each table with a stack of all the values ​​that this value has ever had (each of these values ​​should be temporary). I assume this is similar to setting version control for all my data.

Any ideas on how to do this?

Answer 1

The usual way to track any changes to a table is to add trigger procedures to add / update / delete to the table and save these entries in the history table.

For example, if your main data table is “ItemInfo”, you should also have an ItemInfo_History table that received a copy of the new record every time something changed (via triggers).

This ensures consistent performance of your main table, but gives you access to the history of any changes, if you need it.

Here are some examples, they are for SQL Server, but they demonstrate logic:

My Repository Table My Repository History Table My Repository Insert Trigger Procedure My Repository Update Trigger Procedure

Answer 2

Hmm, what are you talking about sounds similar to Slow resizing .

Remember that version control on arbitrary database structures is officially a rather difficult problem. :-)

Answer 3

A simple solution would be to add a version / revision field to the tables, and whenever the record is updated, instead of updating it in place, insert a copy with the changes made and increase the number of versions. Then, when choosing, always select the record with the latest version. This is approximately the way most of these schemes are implemented (for example, Wikimedia does this to a large extent in this way).

Answer 4

Perhaps the tool will help you do this for you. Look at the following designer:

http://www.nextep-softwares.com

With this IDE, you can take snapshots of your database structure and data and place it under version control. After that, you can calculate the differences between any 2 versions and generate the corresponding SQL, which can insert / update / delete your data.

Perhaps this is an alternative way to achieve the desired.