I am currently working on a large management system (in PHP using the Zend Framework, but this is not really a happy solution to this issue), in which I have to manage multiple entries. Each record has many fields and intervals on two tables in a 1-to-many ratio (via one foreign key). The first table contains approximately 50 fields and 30 fields in the second.
Now I am in the implementation phase of tracking the history of various changes made by users (and some automated tasks). Each record can be partially rejected partially or completely to the previous value.
I was thinking about using a system similar to the one found in CMS Typo3. One table to manage the entire history with the following fields
- history_id
- entry_id
- entry_table
- last_modifcation_timestamp
- last_modification_user
- data
The data will be "serialized" in json or xml format.
My problems with this method are that overtime, the contents of the history table will grow exponentially. To overcome this problem, I thought I could create a new database to manage this story every year, and then show users history data for years.
I am looking for tips on how to improve this solution and facilitate its implementation. Any advice or documents that will help me would be welcome.
source share