Looking for sample code and database schema

[Jeff Cochran]

I've been handed a project to design and implement a survey system for
our intranet. I'm looking for sample code snippets (ASP Classic) or
in particular a database schema someone may have already created to
handle the questions, especially ones such as matrix style questions.
I know there are some great commercial apps out there to do this, but
I can't seem to convince the powers that be that my time is actually
worth anything. :)

Thanks for any pointers, I'll be roughing out the database schema this
afternoon (MS-SQL but it doesn't really matter for the database design
side).

Jeff

[McKirahan]

"Jeff Cochran" <jcochran.nospam@naplesgov.com> wrote in message
news:40393d23.764615198@msnews.microsoft.com...

> I've been handed a project to design and implement a survey system for
> our intranet. I'm looking for sample code snippets (ASP Classic) or
> in particular a database schema someone may have already created to
> handle the questions, especially ones such as matrix style questions.
> I know there are some great commercial apps out there to do this, but
> I can't seem to convince the powers that be that my time is actually
> worth anything. :)
>
> Thanks for any pointers, I'll be roughing out the database schema this
> afternoon (MS-SQL but it doesn't really matter for the database design
> side).
>
> Jeff

I was recently working on the same thing.

I developed a facility to generate surveys from a database.

Here's what I came up with:

Table 1: Survey
SurveyID AutoNumber
SurveyName Text (50)
SurveyText Text (255)
DateCreated Date/Time
DateChanged Date/Time

Table 2: Survey_Questions
SurveyID Number
QuestionID Number
QuestionText Text (50)
QuestionType Text (10)
QuestionOpts Text (255)
DateCreated Date/Time
DateChanged Date/Time

Table 3: Survey_Responses
SurveyID Number
ResponseID AutoNumber
ResponseText Memo
ResponseDate Date/Time


"SurveyName" would be a brief title of the Survey.
"SurveyText" would be a full description of the Survey.

"QuestionType" is "Checkbox", "Radio", "Select", "Selects" (i.e. Multiple),
"Text", or "Textarea".

"QuestionOpts" are either:

a) Valid choices for "Checkbox", "Radio", "Select", and "Selects"; for
example,
"QuestionText" = "What is your formal education?"
"QuestionType" = "Checkbox"
"QuestionOpts" = "High School^Associates^Bachelors^Masters^Doctorate"
or
"QuestionText" = "What is your gender?"
"QuestionType" = "Radio"
"QuestionOpts" = "Male^Female"
or
"QuestionText" = "What is your age range?"
"QuestionType" = "Selects"
"QuestionOpts" = "under 21^21-29^30-39^40-49^50-59^60-69^70-79^80+"

b) The number of bytes of "size=" and "maxlength=" for "Text"
"QuestionText" = "What is your favorite color?"
"QuestionType" = "Text"
"QuestionOpts" = "20^20"

c) The number of "cols=" and "rowss" for "Textarea"
"QuestionText" = "What are your goals?"
"QuestionType" = "Textarea"
"QuestionOpts" = "30^4"

I use the caret ("^") to seperate "Opts".

"ResponseText" contains all of the "joined" Responses for one Survey.
Their relative position in the array corresponds to a QuestionID.

Thus, if the above were five Survey Questions then a "ResponseText"
might be: "Bachelors^Male^21-29^Red^Live and let live."


Does this help you at all? I'll be curious what others offer.

[Bullschmidt]

And you might want to look here:

HotScripts.com
[url]http://www.hotscripts.com[/url]

Planet Source Code
[url]http://www.planet-source-code.com[/url]

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
[url]http://www.Bullschmidt.com[/url]
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Jeff Cochran]

On Mon, 23 Feb 2004 01:08:35 -0800, Bullschmidt
<paul@bullschmidt.com-nospam> wrote:

>And you might want to look here:
>
>HotScripts.com
>[url]http://www.hotscripts.com[/url]
>
>Planet Source Code
>[url]http://www.planet-source-code.com[/url]

I've pulled those, as well as the .NET sample survey, but the database
schema is pretty simple for these. Usually just a question/response,
with no regard for question type. That means a question looking for a
yes/no response ends up as a text field, as does one with multiple
answers as checkboxes, and a free-form comment question. I thought
that was pretty lame for when analysis time rolls around.

Fortunately, this project snowballed (how many times do you hear
someone happy the project expands greatly...?). That means someone
somewhere will be cutting loose a couple hundred clams for a canned
product and I'm off the hook on the programming end.

Thanks for the suggestions to everyone.

Jeff