Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    4

    Customization based on Predefined Templates & How To

    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?

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Attached Thumbnails Attached Thumbnails TertiaryIntersection.JPG  
    Last edited by loquin; 03-08-07 at 15:45.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Mar 2007
    Posts
    4
    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 table
    Code:
    templateid         name
    1                  Pets
    2                  Cars
    template question table
    Code:
    questionid       templateid         text
    1                  1           Do you like dogs?
    2                  1           Do you like cats?
    3                  2           Do you like vans?
    4                  2           Do you like trucks?
    5                  2           Do you like blue trucks?
    template category table
    Code:
    categoryid     name
    1              Pet Likings
    2              Vehicle Type Preference
    3              Vehicle Color Preference
    template question_category linker table
    Code:
    questioid     categoryid
    1              1
    2              1
    3              2
    4              2
    5              2
    5              3
    *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!

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Add a categories table. Set up an additional many-to-many relationship (intersection table) between categories and questions.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Mar 2007
    Posts
    4
    Quote Originally Posted by loquin
    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.

  6. #6
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    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.
    Attached Thumbnails Attached Thumbnails TertiaryIntersection2.JPG  
    Last edited by loquin; 03-09-07 at 16:54.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  7. #7
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Quote Originally Posted by zethon
    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.
    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.
    Last edited by sco08y; 03-11-07 at 21:05.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •