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

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 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 ?

Feb 7, 2025 - 21:02

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 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 ?