title: Database design - Trackable and Dynamic Entity Fields
body: <p>I am creating a table to store data about a Person.</p>
  <p>The requirements are as follows:</p>
  <ul>
  <li>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.</li>
  <li>Track changes - ability to track and revert changes to a specific point in time.</li>
  <li>Great performance</li>
  <li>MySQL</li>
  </ul>
  <p>My idea currently is to have 2 tables, one to define the <code>Person</code> and the other to store <code>PersonData</code>. <code>PersonData</code> will reference <code>Person</code> and include a JSON field to store the data like
  so</p>
  <pre><code>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
  </code></pre>
  <p>The result would be an object merge/replace on the rows with id: 1 resulting in:</p>
  <pre><code>{ name: 'John Smith', address: '5 Main St', state: 'CA', phone: '888 777 6666' }
  </code></pre>
  <p>My challenge is doing the array merge/replace cleanly and ideally natively in MySQL.</p>
  <p>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.</p>