I've been working on a project a lot lately and catch myself walking in to some challenges.
I'm wondering what a good way would be to setup a relational database, while keeping track of older records.
Currently I am using PHP, MySQL, ZF2 and a custom-ish ORM (it kind of grew).
The software I'm working on has a lot of information - Products, brands, customers, addresses, invoices and contracts. The thing is - I want the user to be able to edit customers and addresses, without losing track of the data that was initially set in created invoices and contracts (you can't edit contact information on contracts).
Currently, I have a "static" copy of most tables - once a contract goes to the "fixed" status, it can no longer be edited, and a copy of the customer's information is made into a static table of the customers.
This works, because now the client can still edit the customer without editing existing contracts, and we can still keep track of the underlaying relations between the data.
This however is getting kind of bulky and annoying - big tables - lots of data, basically I setup the static entity management a bit wrong.
Now I've been thinking about other ways to do this, also have been searching a lot, but could not find some proper answers (maybe I'm asking the wrong questions?)
So, what would be a good way to go? I am willing to use existing ORM code - I have been looking into Doctrine, but I'm not sure how to make the "static" copies without making it just as messy as it is now. Another method I've been thinking about was keeping track of changes in tables / rows, and using dates next to a reference to another table - so that you could look up what a value was on a specific date (that would work for invoices as well) - allthough I'm not sure what a solid and simple way would be to do so.
TL;DR: What is a good combination of practices, database design, and maybe frameworks / libraries, to setup a relational database where historical data will be preserved?
Aucun commentaire:
Enregistrer un commentaire