computerforce
Posts: 21
|
| Posted: 01/29/2007, 2:23 PM |
|
It does seem the Survey can be really hard task.
Not an easy thing to make a solution. For example - if you have to make survey with different input types (multiple choice - single answer - radio, checkboxes, essay, ....) means your database tables can be quite complex.
Also - after input, the data has to be inserted into those database tables from forms.
Administration can be really complicated.
Anybody tried to do a project with minimum code, maximum codecharge automatic features?
Maybe the codecharge support team has a solution?
|
 |
 |
wkempees
Posts: 1679
|
| Posted: 01/29/2007, 5:26 PM |
|
Interesting, seems we are facing the same or similar challenge.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/01/2007, 8:06 AM |
|
Well, it seems not so complicated, isn't it?
I am sure many programmers would say that it's an easy task.
For example:
tbl_tests
IDtest
testdesc (description)
IDtesttype (foreign key for IDtype from tbl_types)
tbl_testtypes
IDtesttype
testtype
tbl_questions
IDquestion
IDtest (foreign key for IDtest from tbl_tests)
question
IDquestiontype (foreign key forIDquestiontype from tbl_questiontypes
tbl_questiontypes
IDquestiontype
questiontype (1. multiple choice single answer, 2. multiple choice multiple answers)
tbl_answers
IDanswer
IDquestion (foreign key for ID question from tbl_questions)
answer
tbl_surveys
IDsurvey
surveyfirstname
surveylastname
IDtest (foreign key for IDtest from tbl_tests
surveydate
tbl_results
IDresult
IDsurvey (foreign_key for IDsurvey from tbl_surveys)
IDquestion (filtered on IDsurvey)
result (one number for MCSA, few numbers for MCMA)
This example even does not have tbl_users (survey attendant fills name, lastname in tbl_surveys table).
Any question may have any number of possible answers.
Any survey may have any number of questions.
The main problem is:
When Survey Form is made, it must have something like this:
**********************************************************
Firstname: ____________________
Lastname: ____________________
1. I'll spend my hollidays (one answer - radio buttons):
O California
O Colorado
O Alaska
O Hawaii
O Florida
2. I like sports (multiple answers - check boxes):
X Skiing
X Swimming
X Jogging
X Hiking
X Snowboarding
SUBMIT
**********************************************************
This means:
Not an easy task to have radiobuttons and checkboxes binding directly to table datasource, it has to be written after SUBMIT button is pressed, means it has to be in arrea.
When SUBMIT button is pressed data from area has to be written in table:
For example, attendant did choose Hawaii (27) from the first and Skiing (34) and Snowboarding (38) from the second question (in parenthesis I put IDanswer from tbl_answers), that means in table tbl_results must be two new rows, for example
tbl_result
IDresult__IDsurvey__IDquestion___result
....
33________4________45________27
34________4________46________34,38
....
"," is delimiter for MCMA questions.
The challenge is the Survey Form!
It must save the input data and insert two rows.
CCS cannot do that automatically.
Walter, and others, shall we continue??! 
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/01/2007, 10:15 AM |
|
Or, for insert into tbl_result instead of inserting two rows:
tbl_result
IDresult__IDsurvey__IDquestion___result
....
33________4________45________27
34________4________46________34,38
....
there can be insert of three rows:
tbl_result
IDresult__IDsurvey__IDquestion___result
....
33________4________45________27
34________4________46________34
35________4________46________38
....
"," delimiter for MCMA questions we do not need anymore.
This can make table more .... clean, and much easier for making reports!
I am trying to do this in ASP.NET (VB.NET), but the discussion would be much better if all scripting / programming languages will take part in it.
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 02/01/2007, 3:06 PM |
|
C, (? first name?)
Nice one,you are actually throwing a glove here.
MCSA Multiple Choice Single Answer MCMA .. Multiple Answers.
(I'm Dutch, took me few seconds)
I will have to up the ante a litte, as in my case ( and I imagine yours too) an answer could also change the flow of the survey.
Imagine a Yes No Question, steering to the next or skipping a few questions,the same could be the result of any choice from a MCSA.
Before even thinking tables, I am thinking through the concept.
I envision a "Survey-engine" and an Survey-Adminstration application.
The Administration part
- define survey
- define Q&A plus the type,building a sort of library of Q&A's
- define flow of the survey, like defining logical pages, their content (Q&A library) and the navigational part (what's the next/previous logical page when submit or cancel is pressed)
optionally defining the Q&A related redirecting setting next and previous in the scriptingtable.
- Define storage of the givven answer. this I have still under construction but is globaly based on the "Wizard functionality" generating a unique id and taking that from page to page INSERT/REPLACE ing table data for the results.
The Survey-Engine would be the visitor's part.
The visitor will hit a starting page that will present some inital text or so, but mainly set the session vars that will tell the NEXT what survey to run.
The visitor will (upon submit probably named Next) be redirected to a SurveyPage that takes it's content from the SurveyScriptTable linking all the above together.
It is all a bit rough still, but I could not resist replying.
Benjamin K pointed out to me that it would very well be possible to have several components available on one page (invisible) and activating a control based upon the Question type.
I have also (or am at present) experimented with a label set to html, that will enable you to do almost anything.Grabbing the answers would of course also be handled by the correctroutinefor the given type of Qusetion.
To be continued.....
(I am still reading yoursfor the questions but I am at GMT+1)
But writing multiple rows into a table after one submit is less difficult that you might think.)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/01/2007, 3:58 PM |
|
Well, Walter (I am at GMT+1 also) seems that you already have parts of solution.
Have to say again, that I am trying to do that in ASP.NET, VB.
As you know - PHP has serialize() and unserialize() that can do the perfect work if the radio buttons or checkboxes results would be saved in arrays (I am using PHP.... for a years, not always, but often).
Unfortunately, VB is .... different.
That forced the different approach, but I still think it can be solved thru (similar to what you wrote) dynamic labels (from database tables) and - for the last column of the table - dynamic radio buttons and checkboxes groups, type based on mentioned IDtype.
To make form variables of mentioned radio buttons and checkboxes, which results would be easily inserted thru loop of sql insert commands - seems to be the good solution.
There would be two pages:
First: choose survey;
Second: choose answers.
Complete survey (questions and answers for one survey) would be on one single page.
At the bottom - Submit button, of course, which will trigger insert into database -
insert into tbl_surveys (new IDsurvey, firstname, lastname, datetime, then
insert into tbl_results based on tbl_surveys IDsurvey all checked rows.
The main problem would be the (last) column of radio buttons, checkboxes, how to put them into the table, to look nice, and how to transfer checked items to database.
Seems to be easy, but .... If you read books (I have few names in mind, but do not want to advertise), if you visit developers forums, you'll find a lot of static examples, with fixed number of one type of questions (mostly 4 radio buttons). Many of them even do not support more than one question and four radio button answers only.
To be continued.....
|
|
|
|
wkempees
Posts: 1679
|
| Posted: 02/01/2007, 4:11 PM |
|
C, (Still not on firstname basis  )
I am targeting at it to be as dynamic as possible.
Let's keep at this, there might be glory at the end of the tunnel.
I'll be back
Walter
PS :PhP/ MySQL
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/01/2007, 4:57 PM |
|
in VB there is
<asp:TemplateColumn>
It can be Radio Button and Checkbox column, without databinding.
Okay.... It would be a possibility.
Maybe, there is something general for PHP, ASP, VB.NET.... in common.
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/02/2007, 1:26 AM |
|
Quote :
Benjamin K pointed out to me that it would very well be possible to have several components available on one page (invisible) and activating a control based upon the Question type.
I have also (or am at present) experimented with a label set to html, that will enable you to do almost anything.Grabbing the answers would of course also be handled by the correctroutinefor the given type of Qusetion.
Question type triggers control activation.... Could be really good solution for the Radio Buttons / Checkboxes part.
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/02/2007, 1:19 PM |
|
Walter? May I ask is there anything new?
I am trying to use some asp: (.NET) features for new column in form.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/03/2007, 2:53 AM |
|
I just created one, at:
http://support.yessoftware.com/survey_example/surveys.asp
http://www.yessoftware.com/marketplace/details.php?exchange_id=15
But:
- It is in ASP, not ASP.NET. Uses MS Access as database.
- Only 3 types of questions supported: single selection (radio), text/memo, single checkbox. Didn't have time to play with multi-select checkbox but shouldn't be much more difficult.
- No administration interface.
- Single question per page. Single survey per page could be implemented in similar way via editable grid (I used record form), but there is a logical issue that I didn't have time to look into.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
computerforce
Posts: 21
|
| Posted: 02/03/2007, 4:58 AM |
|
PeterR;
seems like really, really good solution. Converting it to ASP.NET VB and SQL Server should not be a problem.
The multi-select checkbox is one of those fine features many questionnaires need. It's very similar to multiselect listbox, cause user does choose few rows. Means the answer numbers cannot be saved in one number, but in field with delimiters for few answer numbers.
Also, possibility to implement user login, with both possibilities of guests and authenticated users would be really perfect.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/03/2007, 10:59 AM |
|
OK, I'll add the multiple choice option later.
As for the user login, it actually may be simpler than what I did because I am manually creating Session("respondent_id"), while you could just use Session("UserID") after creating a user table and adding a login page.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
computerforce
Posts: 21
|
| Posted: 02/03/2007, 3:49 PM |
|
Multiple choice in answers table would be similar to single choice, but .... control source for it could be problem. To save it in one text field (using join/split in ASP, or implode/explode in PHP), or some other way....
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/03/2007, 4:01 PM |
|
OK, I added the multiple choice option via both multi-checkbox and multiselect-listbox.
Only one line of custom code was needed to save both into single database field as comma-separated values. Also one bug was fixed.
Updated:
http://support.yessoftware.com/survey_example/surveys.asp
http://www.yessoftware.com/marketplace/details.php?exchange_id=15
Have fun!
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
computerforce
Posts: 21
|
| Posted: 02/03/2007, 4:33 PM |
|
Well, I went to the:
http://support.yessoftware.com/survey_example/surveys.asp
it looks clean, simple. Great!
Just one question:
If user goes, using Next button from (for example) second to third question, then presses browser's Back button to go to the second question, chooses something else for the second question answer, then Next again, to go to the third question - would there in the responses table will be two rows for question two? With two different answers?
Anyway this seems to be very good addition for ccs examples package.
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/03/2007, 4:53 PM |
|
Yes, that's what would happen, so a final application may need to handle this. I asume that you may need to cover various other possibilities like users manipulatig the URL, deleting their cookies and re-running the survey, submitting fake info from local computer, not finishing the survey and re-running it from beginning, etc.
My example is just a starting point.
(possibly session variables could be helpful with all this)
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
computerforce
Posts: 21
|
| Posted: 02/04/2007, 2:29 PM |
|
Where's wkempees?
Hope - spent nice weekend....
PeterR;
Quote :
OK, I added the multiple choice option via both multi-checkbox and multiselect-listbox.
Only one line of custom code was needed to save both into single database field as comma-separated values.
For report the most problematical field is multi (char field with comma separated values) not an easy thing to have report without playing with SQL + ASP or PHP extended capabilities.
Maybe, the different approach would help. After submit, for example 3 of 5 checkboxes, there would be 3 inserts (3 new rows) into response table - one for each checked listbox.
For statistics it would be better.
Opinions?
|
|
|
|
peterr
Posts: 5971
|
| Posted: 02/05/2007, 2:52 AM |
|
computerforce,
CCS examples are based on such method (many-to-many) and usually I would rather choose this too. However, in case of surveys we're dealing with several question types and I'm not sure yet how I'd report all results. For example if I'd use custom code in a report to display multiple responses then possibly the comma delimited method could be better.
My actual approach would be to manually create sample data for both approaches (comma delimited and many-to-many) and then try to create a report for each case and see what's easier to work with. If you're doing this for someone else you may also want to get their idea on what type of reports they'd like to see.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 02/05/2007, 4:10 AM |
|
C,
Did have a great weekend.
Peterr, good solid example, thanks.
Will respond later.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/05/2007, 7:48 AM |
|
Text fields are not so usable for reports, but can be used for opinion, comments.
Other fields are usable, may be represented by numbers, then by graphic charts.
SQL command SELECT in combination with WHERE and IN can be used in order to get link between tables response and answers for multiple answer questions. Parameter for IN can be a list of values delimited, for example (1,4,5), or ($datalist) for PHP, and can be inside the PHP or ASP file.
Fortunately, ASP and PHP are sometimes very similar and ways how to sole problems are similar to. At the other side, ASP.NET, is different, regardless of if it is VB or C#. Few of my friends glorify ASP.NET as an absolute, undisputable favourite. I would not say ASP.NET is not good, but ....
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 02/05/2007, 1:41 PM |
|
Just to throw out an idea.
Instead of saving responses in table rows for each question, you could use
an XML object.
this means writing a generic parser to retrieve the data, but it gives you
great flexibility and the ability to "export" the results in one object.
We use this in one of our applicatios, where for example I perform a machine
inventory (OS, applications, etc.). Instead of creating a large set of
relational tables, I store the data in a XML representatio, and to present
it to the user I use a tree control (I am using the Tigra Tree Pro for
this).
The image link below will show you the representation:
http://www.illumen.net/samplexmltree.png
We use this for both system inventories, system event logs, etc.
The beauty of it is that I do not care what the particular data being
retrieved looks like. I just throw it into the blob and then run it through
our generic parser to create the tree.
This enables me to add functionality of querying undetermined data
structures.
Yes, there is a penalty to be paid - yoy cannot query the fields directly
unless you run it through some sort of parser (or unless your particular
database has the capabilities of accessing XML data structures), but the
flexibility just rocks!
The image above shows a detailed view of the results. For the same machine,
the XML of the machine overview looks as follows:
<results> <result>
<var>Manufacturer</var>
<val>Dell Computer Corporation</val>
</result>
<result>
<var>Model</var>
<val>Dimension 2400</val>
</result>
<result>
<var>Asset tag</var>
<val/>
</result>
<result>
<var>Serial number</var>
<val>XXXXXX</val>
</result>
<result>
<var>Processor(s)</var>
<val>1x Intel(R) Pentium(R) 4 CPU 2.66GHz</val>
</result>
<result>
<var>Physical memory</var>
<val>510 Mb</val>
</result>
<result>
<var>Display adapter(s)</var>
<val>Intel(R) 82845G/GL/GE/PE/GV Graphics Controller</val>
</result>
<result>
<var>DVD/CD drive(s)</var>
<val>SAMSUNG CD-ROM SC-148A</val>
</result>
<result>
<var>Network adapter(s)</var>
<val>Broadcom 440x 10/100 Integrated Controller</val>
</result>
<result>
<var>Operating system</var>
<val>Microsoft Windows XP Professional Service Pack 2</val>
</result>
</results>
The same parser renders both regardless of the actual internal content.
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/05/2007, 2:36 PM |
|
B K;
Thanks for the reply;
Quote : Yes, there is a penalty to be paid - yoy cannot query the fields directly
unless you run it through some sort of parser (or unless your particular
database has the capabilities of accessing XML data structures), but the
flexibility just rocks!
Have to say that XML solution involves new facts, good ones, and bad ones, as you said - no possibility of direct queries. XML has really wonderful eXtended features in it's description of the data.
How do you think this specific CSV issue could be solved?
|
|
|
|
Benjamin Krajmalnik
|
| Posted: 02/05/2007, 6:59 PM |
|
It depends on the context of your question, and to some extent what dataase
back end you are using.
You mentioned you re using .Net, so I will assume that you are using SQL
Server.
If you were using PostgreSQL, there are array structures, so assuming you
enumerate the answer, you could have set up an array and increment a field
in a master reply record (or recordset).
One way to approach it in a generic way would be to have a realtime
accumulator which would incement the survey results to a statistics table,
therefore negating the nee for a complex analysi routine to be invoked. You
could do this by saving the results for each question in an XML data
structure, or even relational structures where the strcture of the stats
table could be something alng he lines of:
SurveyID, QuestionID, OrdinalReplyID, Counter
So let's say that for Survey number one, Question #1 he answers could be a
multiresult option. In the surcey results, this could be a CSV set of 1, 3,
and 7.
Now, the last step in saving the result would iterate through the questions
and do the following:
The query culd look something along:
if not exists (Select SurveyID from statstable where SurveyID=1 and
QuestionID=1 and OrdinalReplyID=3)
insert into statstable (SurveyID, QuestionID, OrdinalReplyID, Counter)
values (1, 1, 3, 1)
else
update statstable set Counter=Counter+1 where SurveyID=1 and QuestionID=1
and OrdinalReplyID=3)
Essentially, we are converting the array offset into the ordinal replyid.
The other option would be to store the array in an XML data strcture.
Since the survey is probably structures as a wizzard, th data aggregation
routin would run when the end user finishes posting the survey's last
answer.
The advantage in this is that you do not have to run any sot of analysis
function, since the ata is already there. I use this technique in the same
application which I showed you earlier, where I aggregate statistics in real
time. In my case, I am using PostgreSQL so I am using an array which
greatly facilitate this. That is why I was suggesting using an XML dta
structure, since you can emulate the array.
In that case it wuld look something along the lines of:
SurveyID, QuestionID, XMLReply
XMLReply could look something like:
<xml ..........>
<results>
<result>
<option>1</option>
<counter>3</counter>
</result>
<result>
<option>3</option>
<counter>27</option>
</result>
.
.
.
</results>
You could then retrieve the record based on the surveyid and the questionid,
parse the XML resultset into either an array, a hash, or a scripting
dictionary, aggregate the value, and write it back out.
For presentation, a geneic parser woul be fairly simple to write whic coul
take th data from the XML and create a small table structure which you can
assign to a label inside a td structure.
"computerforce" <computerforce@forum.codecharge> wrote in message
news:2245c7b1637e758@news.codecharge.com...
>B K
> Thanks for the reply;
>
> Quote : Yes, there is a penalty to be paid - yoy cannot query the fields
> directly
> unless you run it through some sort of parser (or unless your particular
> database has the capabilities of accessing XML data structures), but the
> flexibility just rocks!
>
> Have to say that XML solution involves new facts, good ones, and bad ones,
> as
> you said - no possibility of direct queries. XML has really wonderful
> eXtended
> features in it's description of the data.
>
> How do you think this specific CSV issue could be solved?
>
> ---------------------------------------
> Sent from YesSoftware forum
> http://forums.codecharge.com/
>
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/06/2007, 1:16 PM |
|
Have to say that I (personally) do prefer PHP / MySQL (InnoDB) as fast, easy to implement, almost perfect, solid solution.
Sometimes, solutions has to be .... translated. To another platform.
XML seems to be perfect solution for data descriptions. Unfortunately, I do not recognize fast data statistics based on direct queries using XML as part of parsing mechanism.
At Internet there you can see (few) solution attempts, for "converting" CSV columns into rows. Of course, those conversions cannot replace full database normalization. Means, the normalization, PeterR mentioned as the first solution (many to many).
|
|
|
|
Gena
Posts: 591
|
| Posted: 02/06/2007, 2:45 PM |
|
Quote peterr:
Thanks! Just two questions:
1. How about PHP example, please
2. How about Result page?
_________________
Gena |
|
|
|
Benjamin Krajmalnik
|
| Posted: 02/06/2007, 3:31 PM |
|
You may have missed my point.
If I wanted to pull statistics straight from SQL then you would be
absolutely right.
But, more than likely, what you are referring to as statistics can be
updated into a single XML data structure which describes what you need.
Back to the application from which I took the initial structure for you.
The application is a managed services platform. We replcaed packages for
which we paid hundreds of thousands of dollars with a solutin built in part
with Codecharge. We insert over a million records per day, and have
dashboards which need to perform realtime statistics, with many types of
filtering. The only solution which would present us with acceptable
performance needed realtime data aggregation into quasi-static data
structures. In my case, due to the capabilities of PostgreSQL (and the
reason I chose it as a back end) I am using array structures. I could have
used XML descriptors to accomplish the same thing.
For example, it takes me less than a second to aggregate data from over 4000
tests into 3 separate graphs. If I had to issue a query against the raw
data to calculate this, it would take minutes. I have yet to see a
competing product which yields the performance which I have on my platform,
and it is all due to the choice of data structures and having chosen to
perform real time aggregation.
I am not saying this is the solution to your problem, but merely an option
to consider.
|
|
|
|
gerrit
Posts: 131
|
| Posted: 02/08/2007, 2:37 AM |
|
Nice,
Just two questions:
1. How about PHP example, please
2. How about Result page?
3. How about admin page !
Thanks,
_________________
| http://www.vision.to | |
|
|
|
computerforce
Posts: 21
|
| Posted: 02/18/2007, 3:46 PM |
|
Benjamin;
I do not have enough experience in XML, I did not work on big number of practical project which would include XML directly.
Of course last few years I had few small projects where I had to include page based on XML description, and, as I remember, in Visual Studio there were possibility to do that automatically.
I must say that I respect XML absolutely, because of it's flexibility.
If there is possibility for you can you post an example (with details) of how to implement XML in this case here (multiselect checkbox or listbox in one field related to another table of the database)?
|
|
|
|
computerforce
Posts: 21
|
| Posted: 02/21/2007, 5:52 AM |
|
Many visitors asked for the all-platforms-version. With additional features.
Maybe that would be good new example in the example pack.
|
|
|
|
|
