January 30, 2007 at 12:41 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 most welcome!
Richard
January 31, 2007 at 6:25 am
newid() is the function most people use in order to get random order, and random counts use the rand() function.
I had to build the schema and data in order to test:
Create Table Surveys (
SurveyId int identity(1,1) Not Null Primary Key,
SurveyName varchar(50)
)
Create Table Modules (
ModuleId int identity(1,1) Not Null Primary Key,
SurveyId int Not Null References Surveys (SurveyId),
ModulesName varchar(50)
)
Create Table Questions (
QuestionId int identity(1,1) Not Null Primary Key,
ModuleId int Not Null References Modules (ModuleId),
QuestionText varchar(500)
)
insert into Surveys(SurveyName) values ('Favorite Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Breakfast Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Lunch Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Dinner Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Snack Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Takeout Foods')
insert into Modules(SurveyId,ModulesName) values (1,'Chinese Foods')
insert into Questions(ModuleId,QuestionText) values(1,'eggs')
insert into Questions(ModuleId,QuestionText) values(1,'cereal')
insert into Questions(ModuleId,QuestionText) values(1,'pancakes')
insert into Questions(ModuleId,QuestionText) values(1,'french toast')
insert into Questions(ModuleId,QuestionText) values(1,'pizza')
insert into Questions(ModuleId,QuestionText) values(2,'bolgna sandwich')
insert into Questions(ModuleId,QuestionText) values(2,'egg sandwich')
insert into Questions(ModuleId,QuestionText) values(3,'Steak')
insert into Questions(ModuleId,QuestionText) values(3,'Chicken')
insert into Questions(ModuleId,QuestionText) values(4,'pop tarts')
insert into Questions(ModuleId,QuestionText) values(4,'chips')
insert into Questions(ModuleId,QuestionText) values(5,'Subway')
insert into Questions(ModuleId,QuestionText) values(5,'Taco Bell')
insert into Questions(ModuleId,QuestionText) values(6,'Jade Garden')
insert into Questions(ModuleId,QuestionText) values(6,'China Inn')
--questions in random order
select * from questions where moduleid in (1,2) order by newid()
| QUestionID | ModuleID | Question |
| 7 | 2 | egg sandwich |
| 2 | 1 | cereal |
| 3 | 1 | pancakes |
| 5 | 1 | pizza |
| 4 | 1 | french toast |
| 1 | 1 | eggs |
| 6 | 2 | bolgna sandwich |
--all questions related to a random module, in random order
declare @mn int,
@mx int
set @mn = 3 --minimum allowed index
set @mx = 6 --max allowed index
select * from questions where moduleid in (SELECT ROUND(@mn + (RAND() * (@mx-@mn)),0)) order by newid()
| QUestionID | ModuleID | Question |
| 8 | 3 | Steak |
| 9 | 3 | Chicken |
hope this helps
Lowell
January 31, 2007 at 7:31 am
I think Lowell has given some great suggestions. I use ORDER BY NEWID() for randomization as well when we do contests here.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply