I have a database with a
geometry table, each row contains a geometry object, could be a line, a point or a polygon. it also contains the
modifyDate of when it was last modified,
userID of who last changed it and
itemID, a unique id for the item.
in the past, every time an item changes, I just overwrote it. I have recently created a separate table called
geometryHistory so a user can view the changes to an item over time (think a polygon representing an invasive weed).
obviously i can currently only go back a couple of weeks, but I have been taking daily dumps (couple of months missing here and there where the backup was silently failing)
What would be the best way for me to ‘generate’ this history?
My current thinking is to spin up a test server and import all the dumps into new databases based on date eg
then creating a new geometry table without a unique key and loading all the tables from the 500+ databases into it then creating some sort of query that will order by
modifyDate then somehow have it group by
In my mind this should work, but as it is going to be a LOT of work (unless i can script it i guess?) I would like to hear others opinions and whether it is the right direction or if there is a better way.
I know there will be some holes, where an item was changed multiple times in a day or where the backups were not running, but in this case, something is better than nothing.