I'm not entirely sure how to state my question simply, so I will start with some sample tables/data and go from there.

I want to have a database of surveys, that consist of questions, where the questions can belong to one or more categories. Now, when a user logs into the site, he/she will be able to select a "template" survey to conduct and then modify the survey to his/her liking.

For example, the template info will be in a few different tables..

template table - templateid, name
question table - questionid, templateid, text
category table - categoryid, name
question_category_linker table - questionid, categoryid

My problem is, I want to allow the user to customize the survey however is needed. That includes not only changing question text, but adding questions, adding question categories and even linking questions to other categories. And of course, I don't want the template data modified by the user.

The only way I can think to do this is by duplicating the template data. But this seems like a huge waste and bad design. Actually, I know it is.

If I ignore the question category aspect for the time being, the solution seems pretty simple. I'd have two more tables:

user_questions - surveyid,text
user_question_template_linker - surveyid,questionid

In this, 'surveyid' is the survey created by the user. user_questions would store the customized questions and the user_question_template_linker would link the template questions to a user survey. This seems pretty straight forward.

However, when I throw the question category aspect into the mix, it doesn't seem to work as well. How can I accomplish this without data duplication? Thoughts? Suggestions?

I bgelieve you'll need an intersection table to implement a many-many relationship between the template table and the questions table. This intersection table should include the templateID and the questionID, along with the question number (used for ordering) as the PK. In addition, you'll need a Unique Key for the first two.

I believe your Questions table should also include a field to classify the question as a Standard or Custom question.

When you user adds questions to a template, they would simply choose existing questions, or add custom questions) and link the questions to the template.

Now, since you wish to be able to customize surveys, I think you'll also need to include the surveyID (the PK in a Survey parent table) as a part of the template assignment intersection table as well. (you'll be linking together three tables, not two, with the intersection table) This way, your templates would never be duplicated, your questions never need to be duplicated, and you can uniquely join survey to template to question(s), as well as define the order of the questions on a survey-by-survey basis.

the image below sketches this approach.

First, let me say I appreciate your response and your help.

Unfortunately, I'm not sure how your solution helps me with my problem. The diagram you present only addresses the templates, surveys and questions but leaves out the question categories (one question can belong to many categories). But perhaps I didn't do a good job at explaining my problem. Let me present some sample data

Suppose I have a db that looks something like:

template tabletemplate question tabletemplate category tabletemplate question_category linker table*question 5 belongs to two categories

So, these data would constitute a predefined survey (ie. a template). What kind of design can I use to allow a user to customize their own survey based off of either of the two surveys I've created here, without duplicating data. Mind you, the user will be able to create their categories, assign questions to their own categories, change the links, etc.

Again, I appreciate your help!

Add a categories table. Set up an additional many-to-many relationship (intersection table) between categories and questions.

I still don't see how that resolves the problem. But thanks.

does the attached image make it any clearer?

When the user adds a survey, one of the fields to define is the category.

When the user adds a question, they would also assign the question to one (or more) categories by adding intersection records in the QuestionCategory table

When choosing a question for a survey (to insert into SurveyTemplateQuestions,) you could lookup surveys joined to categories, joined to questioncategories, joined to questions to obtain a list of valid questions for a given survey.

Did a similar thing in a project a while ago. Thought the same way you did.

Until I asked myself: logically, what is a template? It's something you make a copy of and then modify. No more and no less.

The copy doesn't "inherit" from the template. It's only "linked" by coincidence to the template. The copy is a separate thing in its own right. (Edititing to clarify: according to the relational model, every entity should be represented once in your logical schema. What I'm saying is that "template" entities and "user quiz" entities are two separate things and thus ought to be represented independently in your logical model.)

There is no good reason to go to a huge amount of trouble when you're a. logically correct and b. paying pennies for gigabytes.

Now, a cleverly designed DBMS will implement Copy On Write semantics, meaning that when you copy a long piece of text from the template to the user copy a special COW pointer will be used instead. The data only gets copied if you write to it (hence Copy On Write) so there is minimal physical duplication of data.

But that's something for DBMS developers to worry about, not DBAs.