Versioning on a table with foreign keys

40
April 16, 2019, at 00:30 AM

Let's suppose I have 3 tables,

Column(ID(Primary Key),name, created_by, description(FK), table_name)
ID | name | created_by | description | table_name
------------------------------------------
1  | c1   | nobody     | 1           | A
------------------------------------------
2  | c2   | abc        | 2           | B
Description(ID(Primary Key), data)
ID | Data
---------
1  | description1
---------
2  | description2
Formula(ID(Primary Key, name, column_id(Foreign Key))
ID | Name | column_id
---------------------
1  | f1   | 1
---------------------
2  | f2   | 2

I need to add versioning to the Column table which holds a new version each time the columns it holds change, or the Formula table changes.

I am planning to construct a master table that will have the columns like this,

History_Column(ID(Primary Key), column_id, column_created_by, column_edited_by, column_desc, column_table_name, formula_name)
ID | column_id | column_created_by | column_edited_by | column_desc   | column_table_name | formula_name
------------------------------------------------------------------------------------------------------
1  | 1         | nobody            | nobody           | description1  | A                 | f1
2  | 2         | abc               | abc              | description2  | B                 | f2
3  | 1         | nobody            | abc              | changed_data  | A                 | f1
4  | 1         | nobody            | cde              | changed_data  | B                 | f1
Description(ID(Primary Key), data)
ID | Data
---------
1  | changed_data
---------
2  | description2
Column(ID(Primary Key), created_by, edited_by, description(FK), table_name, version_id)
ID | created_by | edited_by | description | table_name | version_id
--------------------------------------------------------------------
1  | nobody     | cde       | 1           | B          | 3
2  | abc        | abc       | 2           | B          | 1 

All changes will get updated directly in the tables except I hold all the data in a history table.

Is this a good approach, I am not sure how to deal with the Foreign keys in place. Can someone help me how to tackle this problem statement?

READ ALSO
MySQL count all records that have at least one record on a pivot table

MySQL count all records that have at least one record on a pivot table

I'm trying to look if a record of a table, has at least one record on a pivot table that is used on a many to many relationship

34
table inner join not displaying expected results

table inner join not displaying expected results

I am trying to get a query to display some resultsI double checked the logic to make sure that it is correct and it looks like it should work to me but does not

19
Combining results from two tables into JSON data

Combining results from two tables into JSON data

I have two tables, words and paragraphThe words table is as below:

29
How to query the unmodified timestamp inside a JSON object using MySQL?

How to query the unmodified timestamp inside a JSON object using MySQL?

I would like to query a JSON object containing a MySQL TIMESTAMP named 'time':

29