We have the database design requirement for audit trail purpose in MySQL 5.7.
There is one main table and there are few sub-tables to the main table. For every record in the main table, there will be multiple records in the sub-tables. If there is any change in any of the tables, we want to capture all the information in all the tables for a main table record together.
We initially thought of designing a single table for audit trail which consists of all the columns of all these tables. Whenever there is a change, trigger will capture the pre-image in this table and sub-table records for one main-table record would be stored in one column per sub-table in JSON format.
But we may make the change of 1 million records at the same time. Using trigger is still fine, but manipulation of all the records to put it in a JSON format would kill the performance.
The audit trail is not for reconstructing inserts or to see the entire database changes. If something got changed in any of the tables, we want to identify who changed it and when.