Database design - Trackable and Dynamic Entity Fields

Question

I am creating a table to store data about a Person.

The requirements are as follows:

Dynamic fields - each user may have different data fields stored for them. This needs to be built-in functionality without the need to add columns.
Track changes - ability to track and revert changes to a specific point in time.
Great performance
MySQL

My idea currently is to have 2 tables, one to define the Person and the other to store PersonData. PersonData will reference Person and include a JSON field to store the data like so

PID .... Date ....... Payload
1       1/1/2022     { name: 'John Smith', address: '1 Main St', state: 'NY' }
1       1/2/2022     { address: '5 Main St', state: 'CA' } ---Change address
1       1/3/2022     { phone: '888 777 6666' } ---Add phone

The result would be an object merge/replace on the rows with id: 1 resulting in:

{ name: 'John Smith', address: '5 Main St', state: 'CA', phone: '888 777 6666' }

My challenge is doing the array merge/replace cleanly and ideally natively in MySQL.

Is this a robust and elegant solution, or are the better ideas for how to implement this? I know there are other solutions like Mongo, but we want to keep this in Mysql.

user_7977859 · Answer

Up-voting due to interest (mostly for the "ideally natively in MySQL" part).

Here is how I personally do it in production, if it can help or reassure you :

I have a "history" table :

I think an "event" column saves a lot of headaches. It holds events such as "created"/"updated"/"attached"/etc. When an entry is "created", it looks like this :

When "updated", I hold changes with "before" and "after" keys in an array (json was introduced in MySQL 5.7.8+, I am below) :

This way I can print possibly complex edits in diffchecks/history pages in less than a few milliseconds, for any types (strings/integers/booleans) :

Database design - Trackable and Dynamic Entity Fields

1 Answers