OK, few things... is there a clean up strategy? (30 days old things are removed, etc)
No. A user is allowed create as many versions as they want and keep them around for as long as they need. In reality, they won't keep them for more than a few weeks, but the flexibility is them to allow them to keep them.
How does the tables record count get blown out of whack? What do you mean by this?
I apologize, I said that stupidly. What I meant was, with the current design, the amount of records in each of the 6 seconds get so heavilly balooned when versions begin to be created. It starts to affect performance.
I understand the versionid column, but what non-version related operations are you doing, and if you have indices on those, then what is the real issue? An index isn't a bad thing....
The 6 tables are the backbone of the entire application. There are numerous areas of the app that use the tables to derive calculated data. I wasn't saying having an index on VersionID is bad, but the tables already have numerous other indexes that are required
to speed up selecting records from the tables outside of taking versioning into account.
It seems that if they are performing what ifs and making as many rows as they are, that creating tables for each of those situations will be a nightmare to maintain - even if it is done automatically, because now you'll have to know which table to go into,
and looking at the table from a dbo perspective will be much much harder to aggregate and manage.
If there is Real data and WhatIf data, then instead of making tables for each version, I'd make a set of six tables for the WhatIf scenarios and put all of the user generated stuff in there. Then the live data is where it needs to be, and the what if can be
separated off (and you can do cleanup operations on there at intervals, or however you want to do it).
Knowing which table to look to is simple because in the business logic I'll know whether a user is currently within a version and if so, what the version ID is. With that I can easily direct the query at the versioned table as opposed to the live table.
I agree that having a live and "what if" set of tables would be very clean, but my concern is that performance would still degrade over time. The live tables would stay in tact and work nicely, but as users are creating more versions, selecting from the "what
if" table will get slower and slower due to the massive amounts of data. Plus once a version is deleted, performing the DROP TABLE operations on the versioned tables would be almost instant, as opposed to having the delete the records from all 6 "what if"
tables which would affect a lot of records, causing the tables indexes to need to be rebuilt.
If you put those tables in a different database, like "Live.Table1" and WhatIf.Table1" then you get the benefit of being able to use the same sprocs for both scenarios so you do not have to rewrite all of the 'what if' sprocs, so it is as close to production
I was already planning on using a seperate database.