How to organize "master" data VS "working" data in MS SQL

Question

I have this survey software that I'm writing and I'm wondering what would be the best design for my requirement.

I'm going to simplify it as best as I can.

I have these entities:

class Survey {
 public Guid Id { get; set;}
 public string Name {get;set;}
 public ICollection<SurveyQuestion> SurveyQuestions { get; set }
}
class SurveyQuestion {
 public Guid Id { get; set;}
 public Guid SurveyId {get;set;}
 public string Question { get; set;}
 public string Answer {get;set;}
}

A user obviously can design surveys and its questions, but also can start an "instance" of a survey.

When an instance is started, I'd would like it to stay as it is and not change if the "master" definition of the survey/questions is changed afterwards.

So I thought of having an entity like this:

class SurveyInstance {
 public Guid Id {get;set;}
 public Guid SurveyId {get;set;}
}

When it is created, I would create an SQL schema with the returned Id of the created instance, then create both Survey and SurveyQuestion tables under that new schema and insert the relevant data in them.

That way, when a user resumes the survey, there are no risk of breaking changes if the "master" data changes in the meantime.

Obviously, the Answer property of SurveyQuestion will only hold a value when the row is present in a table under a schema other than dbo.

When the survey instance is deleted, I would drop all the tables under its schema, then drop the schema altogether.

Are there other approaches, pitfalls, problems I'm missing ?

• Greg Burghardt: Do you have a specific problem with this design? Questions asking for general feedback about a design are usually closed as being opinion-based or needing focus. You have a lot of good information in your question, but I don't see an engineering problem to solve.
• Francis Ducharme: Well, I guess it can be closed because other people's opinion is exactly what I'm looking for. I was just trying to see if there were any other designs that would be better than mine.
• Greg Burghardt: Unfortunately opinion-based questions do not fit the Q&A style of this community. You might try Software Engineering Chat or more general forums on other sites.
• Doc Brown: Well, I actually see a few specific questions you could ask in this question which might be answered in a non-opinionated manner. Not sure if you really want to ask them, though. You could, for example, ask for a design which avoids to create and drop tables while the program is running. Or for a design which avoids to repeat the storage of the same question text for each person answering the survey, while you still have questions "frozen" once the survey was started.
• Ccm: Have you considered nosql instead?

Answer 1

CoW

This sounds like a copy on write situation.

You have an evolving table such as SurveyQuestion, and you'd like for various callers to take a reference to its current snapshot.

For simplicity I will assume the Survey table is append-only. That is, we INSERT, we don't DELETE, and we never assign a new name to an existing survey. With these restrictions we can still capture much of the intended business use case.

The SurveyQuestion detail table is quite mutable, supporting Q's & A's that authors keep editing / deleting / adding. There's a FK relationship to Survey.

Invent a third level of detail, a SurveyAnswer table, with FK to SurveyQuestion.

Now deleting a Survey or SurveyQuestion row will cascade through to one or two levels of detail records. Avoid making UPDATEs to those two tables, preferring to INSERT new rows when e.g. correcting a typo. That way existing references are associated with frozen unchanging text. Feel free to run a daily cron job that deletes master rows having ancient timestamps or zero child detail rows.

• Anton Pastukhov: This should be the accepted answer