Well, I guess you are creating a kind of versioning.
It is on the roadmap but maybe not before 10 months (more or less)
If I understood, you want is to be able to keep in DB all singles modifications of the item entry (the versions)
A versioning system permit to
- virtually go back in time (time machine like)
- virtually ignore an author, and display others versions of the item (last in time)
- keep and show current values before a content has been approved.
- have fallbacks when item is not found (used for languages for example)
- ...
Well, I never studied what is the best method to realize it, so my advices can be wrong.
I show you the simpliest, but not the best. (really simple use)
TODO :
- Use only one table !
- Create additional fields in your table (fk_parent, creation_date)
- fk_parent is pointing the same table (recursive, but uses only 1 level)
- creation_date : Implement the Wizard to create it.
Then you code something that, instead of updating the item, it creates a clone in DB.
This clone store in fk_parent, the id of the original item (Let's say ALWAYS the first version of it, it is more easy)
Change the listig query to filter and get ONLY the last versions items. You can struggle here. Try to create it ONLY in SQL (no php itteration please !)
Maybe some extensional fiels can be created (ex: current_version, approved, etc...) to help you building a fast query. And depending what you really want.
If you are storing a lot of datas, you need to consider it !
So you may need to clean sometimes your versions, but always keep the original row of the item, updating it with the last version values, when you clean it.
I am almost sure it exist a better system. A kind of 'strategy' to do it correctly and powerfull.
If you find a good method, please propose it here.
Anyway, you really need to code, there are no others solutions for the moment.
