DB Design Question - Grouping, Ordering & Randomization

Last Post 30 Jan 2007 06:37 PM by richardrogers. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
richardrogers
New Member
New Member

--
30 Jan 2007 12:50 PM
Hello,

I am modifying an existing database used for online surveys.
A Survey is composed of multiple modules. The modules contain questions. As a result, I have the following tables defined:

Create Table Surveys (
Id T_PkFk Not Null Primary Key
...
)
go

Create Table Modules (
Id T_PkFk Not Null Primary Key
, SurveyId T_PkFk Not Null References Surveys (Id)
...
)

Create Table ModuleQuestions (
Id T_PkFk Not Null Primary Key
, ModuleId T_PkFk Not Null References Modules (Id)
, QuestionId T_PkFk Not Null References Questions (Id)
...
)

When the designers are creating a survey, they need to be able to specify the sequence of the modules for each different respondent that takes the survey.

For example, lets say Survey1 contains 10 modules, Modules1 - 10, and there are 18 questions to this survey.

The survey programmers need to be able to sequence a survey, so that:
1. Modules 1 and 2 are always seen first, by all respondents.
2. Some respondents will complete the group of modules (3, 4, and 5) before the group of modules (6, 7, and 8), but some respondents will complete the group of modules (6, 7, and 8) before the group of modules (3, 4, and 5). This is an example of module order randomization.
3. Some respondents will complete Question 14 before Question 15, but
some respondents will complete Question 15 before Question 14. This is an example of question order randomization.
4. Question 16 has 10 choices, and some respondents will see choices 1 through 5 first, whereas other respondents will see choices 6 - 10 first. This is an example of question choice order randomization.

These are just examples. I'm looking for some table designs that would permit these kinds of "rules" to be put in place.

Any ideas or experience in this area are seriously welcome!

Richard
richardrogers
New Member
New Member

--
30 Jan 2007 06:37 PM
Hi Russell,

The crux of the problem is that the sort order is only known after the survey is created, and will change each time a survey is created. If the sort order was the same for each survey, the select statements you showed would be a good start.

I'm thinking more along the lines of creating a table like this:

quote:


Create Table Sequences (
Id T_PkFk Not Null Primary Key
, ModuleId1 T_PkFk Null References Modules (Id)
, ModuleId2 T_PkFk Null References Modules (Id)
, QuestionId1 T_PkFk Null References Questions (Id)
, QuestionId2 T_PkFk Null References Questions (Id)
, QuestionChoice1 T_PkFk Null References QuestionChoices (Id)
, QuestionChoice2 T_PkFk Null References QuestionChoices (Id)
, GridHeadingsId1 T_PkFk Null References QuestionChoices (Id)
, SequenceTypeId T_PkFk Not Null References Lookups (Id)
)



Then when the survey designers need to setup the sequencing of the survey, the table would end up with something like this:

quote:


Id ModuleId1 ModuleId2 QuestionId1 QuestionId2 QuestionChoice1 QuestionChoice2 GridHeadingsId1 SequenceTypeId
----------- ----------- ----------- ----------- ----------- --------------- --------------- --------------- --------------
1 1 2 37 <--- P1
2 3 4 38 <--- P2
3 4 5 38 <--- P2
4 6 7 38 <--- P2
4 7 8 38 <--- P2
5 3 6 39 <--- P2
6 14 15 40 <--- P3
7 1 2 42 <--- P4
8 2 3 42 <--- P4
9 3 4 42 <--- P4
10 4 5 42 <--- P4
11 6 7 42 <--- P4
12 7 8 42 <--- P4
13 8 9 42 <--- P4
14 9 10 42 <--- P4
15 1 6 43 <--- P4


The SequenceTypes would look like this:

Id LookupTypeId Active ShortDescription LongDescription
----------- ------------ ------ ---------------------------------------- ------------------
You are not authorized to post a reply.

Acceptable Use Policy