0

I have a Person with columns like name, address and bio. How do I store data in order to know the "freshness" of these columns?

For instance, "for the Person Y, the name has changed 2 days ago, and the location 2 weeks ago".

Is this simply a versioning problem?

Person v1 - name: A1, address: B1, bio: C1, updated_at: 4.days.ago
Person v2 - name: A2, address: B1, bio: C1, updated_at: 3.days.ago
Person v3 - name: A2, address: B1, bio: C2, updated_at: 1.days.ago

And after I look trough different versions to find the last one with a different column?

Or is there better ways to handle that? Put freshness data in another table? Or even DB?

I will normally use MySQL, unless this can be solved by means of another database management system.

Regards.

Additional details

Thank you for you comments and answers, but I was forgetting something: Actually, I need to know when the column value was updated, but by who as well. Something like "updated at XX by User Y".

Community
  • 1
den
  • 1
  • 1

2 Answers2

0

With MariaDB 10.3 you can use system-versioned tables.

You can then see all the versions of a particular row with a query like:

SELECT *, ROW_START, ROW_END FROM Person WHERE id = 4 FOR SYSTEM_TIME ALL;

Also, with the AS OF syntax you can get the version of the row that existed at a particular time:

SELECT * FROM Person WHERE id = 4 FOR SYSTEM_TIME AS OF TIMESTAMP '2019-08-08 15:10:00';

See also this post in the MariaDB blog: Automatic Data Versioning in MariaDB Server 10.3.

EDIT

If you need to know who made a change, this will probably have to be controlled through your software. You can add a special last_change_by column in the table e.g. something like:

ALTER TABLE Person ADD last_change_by varchar(100);
dbdemon
  • 6,964
  • 4
  • 21
  • 40
0

From what you are looking at you need to create an audit system that will record several points of data about every insert/update/delete for the information you want to know. You can set it up so it monitors every column or just the ones that a user can change or you care about knowing when they changed. This will provide an added benefit of using less storage space when only one or a few columns change or if there are not many columns that the user can change to begin with.

Audit Information

  • Table where data is changed
  • Column that was changed
  • Date/Time of change
  • User who made the change
  • Old value
  • New value
Joe W
  • 1,058
  • 9
  • 20