DB Design Question - Grouping, Ordering & Randomization

Question

Post reply

DB Design Question - Grouping, Ordering & Randomization

Procmeister

SSC-Addicted

Points: 432
More actions
January 30, 2007 at 12:41 pm

#117461

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

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

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

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 2

I think Lowell has given some great suggestions. I use ORDER BY NEWID() for randomization as well when we do contests here.