Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to represent surveys with questions like these ones:

enter image description here

enter image description here

enter image description here

Studying those groups of questions I found these rules:

  • A survey has questions' groups.
  • A questions' group has a name and questions (all question will have the same number of options).
  • A question has a question text and a group of possible options to answer it.
  • An option has a name and a type.
  • A type could be: Text, Yes/No or Checkbox.
  • An answer is a value that user gives to an question's option for a given survey.

Do you think that those requisites are OK?

With, those requisites I have design the following conceptual model (here I call form instead of survey):

enter image description here

I need to make surveys dynamics. I'm going to reuse group of question, questions and/or options.

share|improve this question
    
Perhaps you want to have a separate Option_types table have have your Option table refer to it with an Option_Type_ID foreign key? –  FrustratedWithFormsDesigner Apr 4 '12 at 13:50
    
this question on so may help: stackoverflow.com/questions/4965317/… –  DForck42 Apr 5 '12 at 5:43
    
This answer may help - dba.stackexchange.com/questions/16002/… - covers similar material. –  Michael Durrant Apr 21 '12 at 13:29

1 Answer 1

I offer a more complete model, from an actual solution.

The 'type' is associated to the question per-se (not to its items, as stated at the fourth item from the question).

A question may be of single-line or multiple-line textual box or composed by several items. For each item may be assigned a complementary question (eg. 'Country?' - 'Other' - 'Which: _').

HOW the question and its items will be presented to the user depends on their type.

The model has been made flexible enough to represent several structures of questions.

The 'FormQuestionType' entity has an attribute to identify the ASCX (ASP.net component) responsible of rendering questions of this type.

It's possible to make a question dependent of one or more of the previous ones (eg. 'How many children?' may be made dependent of 'Do you have any children?').

There's prevision for data validation, from 'required' constraint to regular expressions.

For every major entity has been defined the mandatory and unique attribute named 'identification', allowing queries based upon textual codes instead of numerical ids (eg. 'How many children?' question, of id '351', is also identified by 'HOW_MANY_CHILDREN').

And, for the items of a given questions, there may be defined an external source of data (eg. 'city' question has its options retrieved live from corporate 'city' table).

The actual model provide versioning, but, for clarity's sake, I present a stripped down version of it.

Dynamic form model

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.