Hi, here comes a rather general query regarding data management for an ongoing project.
(If it is better suited on another part of these forums please advice/move)
We're looking at organizing one or several enterprise geodatabases (SQL Server 2016) for land use data. This data consists of several feature classes grouped into several themes. This data is typically updated continually, by several users at the same time, with a new definitive final state of all data produced once every five or so years after work is focused for a year, say(a "revision"). This state needs to be saved. During the year work focuses in order to produce a definitive state two intermittent states are produced, these also need to be saved and somehow associated with the year's definitive final state (and the associated year).
Saved states need to be easily accessible. It should be possible to compare them, if only manually.
Feature attributes and geometry will change, as may the feature class schemas. A column might drop or be added, a feature class might be dropped or added. Versioning is needed to acommodate several editors; one database and archiving cannot be used as archiving will among other things not save removed columns.
We were thinking of using one db per revision, with one dataset per intermittent state and then the definitive final state. All feature classes would be duplicated in all the datasets, except if removed/added, though they may have been edited(rows and schema).
Land use original state: copy to db:land_use_rev1, initial state dataset-> multiple edits and/or schema changes-> intermittent state 1 dataset -> multiple edits and/or schema changes-> intermittent state 2 dataset-> multiple edits and/or schema changes-> rev 1 final state dataset
rev 1 final state dataset : copy to db:land_use_rev2, initial state dataset -> multiple edits and/or schema changes-> inter......
(Since the initial state dataset will be identical to the final state dataset of the previous db, the initial state dataset can be deleted as soon as the first intermittent state is reached.)
The reason for three copies of mostly the same data per database and not archiving or just different transactional versions for the states in each database is because geometry and attribute and schema differences need to be discernable between the saved states.
We're not enterprise geodatabase modeling experts but have read up some on transactional versioning and the archive function and I'm posting here in order to perhaps receive feedback that this looks totally off the rails or that it indeed seems like a viable solution.
Thanks for reading : )