build web form advice 2

[evr72]

Hello,

I am going to start working on a project to build a form, I know a little of asp, html and javascript.

basically the form will be divided into 5 or 6 different tabs, each tab has around 200 questions or so.

Not sure how to structure the whole thing.

I was thinking in hving my data in access, build 2 tables one that has the username, password and company this would be my users table.
Then another table for the questions and answers?

but then not sure how to structure this table
would it be something like

Questions yes/no longanswer username?

I know i am not really asking an asp question, but was not sure where to post this, I apologize if this is the wrong forum.

 

[gmmastros]

I would suggest 3 tables.

Users
-----
UserId (integer)
UserName
password
company

Questions
---------
QuestionId (integer)
QuestionText

Answers
-------
UserId (integer)
QuestionId (Integer)
Answer

Each user would have one row in the Users table. Each question would have one row in the Questions table. The Answer table would store a single row for each question answered by each user. So, if you have 200 questions and 8 users, the Answers table would have 1,600 rows in it. Notice that I am recommending integer id numbers. Your users will never see this value, it is only used by the application. But... by using Integer Id's, the table with the most rows will have less data because the text (username and question) will not be repeated for multiple users.

This structure also lends itself well to future modifications. For example, you could add a Survey table, like this:

Surveys
-------
SurveyId (integer)
SurveyDescription
SurveyStartDate
SurveyEndDate

And then another table like this:

SurveyQuestions
-------------
SurveyId (integer)
QuestionId (integer)

The idea here is... you will be able to create a completely new survey simply by modifying data in a table instead of creating new web pages and/or tables to store the data. For example, you could have the system check the date and return the correct survey as soon as someone logs in. You could then get the list of questions to use based on the SurveyQuestions table, and then get the questions themselves from the Questions table.

Built properly, you should be able to create an entirely new survey by changing the data in a couple tables.

By the way, your real question is actually about database design. If you have never heard the term [google]database normalization[/google], I strongly encourage you to spend an hour or so and do so research on it. You'll be glad you did.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[evr72]

gmmastros,

I really appreciate the input.

I started my reading on database normalization.

now the question is. I will build a log in page, the user will go to the form. Do I capture the user id then? and pass the value to the answers table?

 

[BigRed1212]

You have an enormous project.

If you are seriously going to ask users to answer 1,000 questions, you need to think about the mechanics a little bit. 200 questions on one page with one submit button is too much.

And the last thing you want is for a user to have to hit submit on everyone of 1000 questions.

I hesitate to recommend AJAX if you know only a "litle" but I think something like that, with "instantaenous" database updates, potentialy no submits, and a desktop application feel for making changes may be something you want to look into.

Upshot is that with the planned task, user considerations may need to drive some of your decisions.

 

[evr72]

BigRed1212,

Thanks for the comments, this has made me re-think the way we will deploy this form. It will be in stages, this is a long term project.
Where I am having a mental block is in my db design.

I know I am going to have my 3 tables users, questions and answers.
What not sure is how to go about assigning the questions to each user.
Do I join my tables? questions join to answers by questionid and then users join to answers by userid?

then how do I insert the userid and questionid into the answers table?

 

[BigRed1212]

What not sure is how to go about assigning the questions to each user."

That is another wrinkle. Are you talking about a "custom" test or someting so that each potential user might see a different set of questions based on some criteria?

Big George's data structure and comments about normalization will serve you well if this is way you are headed.

Using that structure, a list of all the questions might be:

select *
from questions

To have a group of questions (think a survey!) for a particular user I think you might want the surveyquestions table above and additional table called maybe userquestions with two fields: userid (from users) and surveyID (sID from surveyquestions).

You could create as many different question packages (surveys) as you wanted and then assign them to individual users using the userquestions table.

To show Bob (userid=3) "his questions" (which are really from whatever survey group you have assigned him to) your query might be

SELECT surveyquestions.qid, questions.qtext
FROM questions INNER JOIN (surveyquestions INNER JOIN (userquestions INNER JOIN users ON userquestions.uID = users.uID) ON surveyquestions.sID = userquestions.sID) ON questions.qID = surveyquestions.qid
where users.uid=3

 

[BigRed1212]

then how do I insert the userid and questionid into the answers table?"

Read up on INSERTS at

http://www.w3schools.com/sql/sql_insert.asp

INSERT INTO answers (userid, questionid, answer)
VALUES (3, 4, "I think so")

 

[evr72]

BigRed1212

I really appreciate it for taking the time to answer my questions. All the users are going to see the same questions
I have played around with sql and I am able to separate all the questions
I have also build a couple of simple forms and know how to do an insert. Where I am having a mental block and sorry for being so thick headed.
If I have the users table, the questions table and the answers table
do I build my form and when I submit it I do the insert userid and qid?

In my first try I used this sql statement

SELECT
questions.category,
questions.questionid,
questions.questions,
answers1.answer,
users.userid
FROM (answers1
RIGHT OUTER JOIN questions ON questions.questionid = answers1.questionid)
LEFT OUTER JOIN users ON answers1.userid = users.userid

This shows me all that I need, but when I added the log in script

whereusers.userid=5

I got nothing, and that makes sence because my answers table is empty
so do I send the userid and qid on submit? here is where my mental block is

 

[evr72]

or does each user has to have a question id?

but that would be too much every time you wanted to add a question?

 

[gmmastros]

You shouldn't need to join to the users table because the only piece of information you want is the user id, which you already know. Right?

SELECT
questions.category,
questions.questionid,
questions.questions,
answers1.answer,
[!]5 As userid[/!]
FROM (answers1
RIGHT OUTER JOIN questions ON questions.questionid = answers1.questionid)

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[evr72]

In my answers table I do have a userid field but it is empty, So do I need to populate it with all the user id's?

 

[BigRed1212]

Are you thinking of the answers table as the incoming answers to questions as provided by users or as the outgoing choices on a multiple choice question?

Mr. Mastros' design and my follow (give him the star- not me) I believe assume the answers are where the responses from the users are stored.

Are you wanting a table of response choices you can offer users?

 

[evr72]

the questions table has the questios the user will see and the answers table is where the responses are going to be stored

 

[gmmastros]

It appears as though you are using Microsoft Access. I am more familiar with Microsoft SQL Server. What I am about to suggest is possible using Access, but my syntax may be a little off.

I suggest that you ONLY add rows to the Answers table when a user actually answers a question. This gives you a little more information than you had before because you can determine which questions have not been answered by a user. To get this information, you write a query starting with the questions table and joining to the answers table. If the row does not exist in the answers table, it indicates an unanswered question. The query would look something like this:

SELECT
questions.category,
questions.questionid,
questions.questions,
answers1.answer,
5 As userid
FROM questions
     left join answers1
        On questions.questionid = answers1.questionid
        And answers1.userid = 5

Since this is Access we're talking about here, I'm sure there are missing parenthesis from that query. But, the important part to realize is that the answers1.userid MUST be in the join clause and not the where clause. If you put it in the where clause, you will not return the rows you want and (in fact) the query will behave like an inner join instead of a (left) outer join. The query I show above should list all of the questions and answers. If the user has not answered the question, then answers1.answer will be NULL. If you want to get ONLY the questions that have not been answered, you can add a where clause criteria that checks for NULL, like this...

SELECT
questions.category,
questions.questionid,
questions.questions,
answers1.answer,
5 As userid
FROM questions
     left join answers1
        On questions.questionid = answers1.questionid
        And answers1.userid = 5
[!]Where answers1.questionid is NULL[/!]

Again, you should willy nilly toss about some parenthesis to accommodate Microsoft Access. Rest assured that the idea is sound, even if the syntax isn't.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[evr72]

gmmastros,

I tried your code, when I run the sql statement it works well, but when I build my add page I cannot see any of the records, I think this is because when the user logs in it takes him/her to its own records and since they have not filled anything out, there are no records. So it looks like I need to add each questionid and user id to my answers table

SELECT
questions.category,
questions.questionid,
questions.questions,
answers1.yesno,
answers1.answer,
answers1.userid
FROM questions
LEFT OUTER JOIN answers1 ON questions.questionid = answers1.questionid
Where answers1.questionid is NULL

I am using ms access in the backend for my data and asp to bring in the data to the db

 

[BigRed1212]

Actually, I don't believe what you have posted is Mr. Mastros' actual code, but perhaps I am wrong.

What exactly are you wanting to do here? What is your "add page" supposed to be doing? Do you have questions in your database? Have you written a basic query to display questions regardless of the user? Have you taken the small incremental steps or are you trying a big leap?

 

[evr72]

Mr. BigRed1212, Mr.Mastros

Please accept my appologies, rookie mistake. I copied my users table and the userid became text, although I was not getting any errors when I went back and took a look at the tables and why it was not working the way I expected I saw my mistake. I have fixed it and I believe it is working, have to do a bit more testing. But it is a great start!!!!!

Thank you for all the advice and help!!!!!!!!!!!