How to keep a history of changes to entities in the system?

Question

MySQL

I am writing a small ServiceDesk for the needs of my PHP + MySQL company. Tell me what you do when you need to keep a history of changes to entities (tasks, customer cards, etc.) in the application. For example, an application is transferred from one employee / department to another, it is necessary to keep a history of changes in the responsible person, the date of acceptance by various employees, dates of transfer between employees / departments, etc. on this task. Is it stupid to create a table for the history of changes or is there some special approach for this functionality?

Answer 1

Add the DBEG as datetime field to the table containing the information about the entity.
When creating an entity, enter the current date/time in this field. When changing an entity, do not change the entity record, but add a new record with the current date/time value in DBEG. The selection of the current entity will take place like this:
select top 1 * from table where ID= order by DBEG desc
Thus, the entire history is saved, well, it would not be bad to have an interface for viewing the history.
This is the general approach. If it is necessary to implement the storage of changes for certain attributes of an entity, then such attributes must be separated into a separate table and a similar approach should be applied in this table, while the entity itself will be in a single copy, but some of its attributes will be with history.
Attributes can be allocated to a table by data type, let's say a separate table for integers, strings, real, dates, etc. Or one common table with the appropriate fields for each type of data.

Answer 2

> Stupidly a table is created for the history of changes Smarter is
better :) It
depends what exactly you want to do, ie. what are the business requirements.
Option 1:
you just need to have a log about what has changed. In this case, you can simply write a log for any change:
who changed, when, what changed, old value, new.
Option 2:
you need to be able to roll back to the specified state at any time (like a wiki, for example).
Then do p1 and add the functionality of working with logs.
But the most simple thing is to just make a log.