I have a database to hold data about
Projects have lots of data associated with them that is segmented into other tables such as
project_images. Those are one-to-many relations where 1 project can have many contacts and many images.
However, I need to be able to keep "versions" of projects. So, if a project is updated, the previous data about the project is still present. This is a large issue with many-to-many relationship. For example, there is a
categories table, and a
project_categories table - many projects can have many categories.
When a project updates, not every piece of data will be updated. For example, if a project gets a new contact, it won't always have a new image, so the images table won't be updated.
One solution I have considered is to have a
versions table with an auto-incrementing ID; a
project_id column and a
time column. Each table will also get a
version_id column. Whenever a project is updated, a new row is created in
versions. Then, when the tables are updated, the
version_id gets entered too. This means that the latest row about a project in
project_images might be 3 whereas the latest version in
project_contacts might be 27.
version_id is incrementing, it's really simple to get the latest version of a single project, as you can join all the tables by the highest
Where this idea seems to fail for me is if I want to get the version of a project from a specific date.
Could I use my current solution to address that problem?