Creating a database sandbox/testing area for users to run what-if scenarios

Question

I'm trying to figure out how to create a "sandbox" area in my new Windows .NET/SQL Server application. Here's the requirements:

Users must be able to enter their own data for things like date ranges and pricing Users must be able to run multiple scenarios against this pricing and date range data

The data structures above will also be used in the regular production database. So on the one hand we'll have all the structural information built, on the other every user (from 10 to 30 people currently) needs to be able to create their own testing scenarios.

What I'm not sure about is the best way to do this. I supposed I could give everyone local copies of SQL Express and make sure the local copies reflect the data structure of the production data, but this seems messy and less than ideal. I don't want to create many copies of the database on the production server to give everyone their own playground either.

Is there something obvious I'm missing here? The requirement that the users be able to enter their own configuration data for prices, date ranges, etc., is the thing I'm really stuck on. They need to run these "what if" scenarios against their own settings, but the production system can't have this kind of stuff in there.

Temp tables may be out because these scenarios may need to be saved across sessions, like when they leave one day and come back the next or whatever.

Any pointers or suggestions would be greatly appreciated.

Answer 1

Separating everything out into client databases sounds like a bad thing. You want to be able to run these scenarios in the main application database.

Just concentrate on being able to create a scenario and leave out the multiple users for a second.

You don't mention what the prices are for, but let's say you have a PRODUCTS table.
Let your user set up a new scenario and give it a name, which is then assigned an ID.

You could create table in a separate schema (optional) - scenario.PRODUCTS table and let people insert substitute information about products into it. Then you could create a UNION of all the PRODUCTS minus the ones that are in the scenario.PRODUCTS table based on the scenario ID and product ID, which will give you the users custom pricing information + all the current pricing.

You could set up security on these scenarios to give certain groups of users access to them, or let the user selectively grant access to them.

You could also create a separate database to do this in and import the production data nightly if it's a busy system - You would leave all the data in the scenario schema so your users data persists. Running these kind of forecasting things can cause performance issues.

This is all theoretical thinking on my part - hopefully it helps to get the wheels turning.