Questionnaire Table Design

[GrexP]

This is not a big challenge, it’s more of just trying to decide which is the best way to do it. I need to input data from a questionnaire. We have 200 hundred or so clients and each client has a several hundred customers. The customers are asked to fill out a 10 question questionnaire.

Each question has 5 answers, which will be given a value from 1 to 5, with one being the worst and 5 being the best. I don’t recall the answers exactly, but for the first 5 of the questions the answers are something like….

Very Poor
Poor
Ok
Good
Very Good

For the last 5 questions the answers are something like…

Never
Not Often
Seldom
Regularly
All The Time

This is going to be an annual thing, and the questions might change, so I’m trying to make the code and tables as transparent and reusable as possible. I set up a table for the answers like this.

ClientID – Integer
AnswerSequence – Integer
Answer – Integer

The AnswerSequence will be 1 through 10 to represent which question, and then the Answer will be 1 through 5 to represent the answer.

I have a look-up table with the text version of the answers. There are 2 fields, with one being Integer and one being text. The integer values for the answers are used to build queries to populate listboxes with the answers for data input. It looks like this

1 - Very Poor
2 - Poor
3 - Ok
4 - Good
5 - Very Good
6 - Never
7 - Not Often
8 - Seldom
9 - Regularly
10 - All The Time

The problem is, if question number 8 has an answer of 2 it should equate to “Not Often” because it’s in the second half of the questionnaire. However, because the value is 2 the answer will come back as “Poor” from the look-up table.

If I add to the value as the data is input I could add 5 to the value of questions 6 through 10 and then I could retrieve the correct answer from the look-up table. However, that could throw off the reporting because we may want to give average scores. If that’s the case I would need to subtract 5 from the answers for all questions 6 through 10.

These are simple solutions that seem inelegant. Another option is to add a forth field to the answers table that would join to the look-up table. The answer for question 8 would be 2 and then there would be a value of 7 in the 4th field to retrieve the text version of the answer. For that matter, I could just store both the value 2 for the answer and then have a text field as well.

Any other ideas?

 

[Banana]

Well, there's several way to do it.

Keeping your present structure, you would just change the form presentation so a user select from a listbox that only show first half of possible answers and when they get to a question, listbox can be changed to show only second half of possible answers.

But I'd think it's more preferable to normalize your structure a bit. I'd have the following structure:

tblAnswerType -- A listing of all possible groups. You'd add two Types (Poor-Good and Rarely-Often)

tblAnswers -- A listing of *all* answers, but with a foreign key to the tblAnswerType to tell you which group it belongs to.

tblQuestions -- A listing of all possible questions. It also has a foreign key to AnswerType to tell you which answertype should be used.

And if you want to be able to re-use questions for another survey, make a table of Survey and make a many-many relationship between Survey and Question.

This way, the listbox form I described you is now totally abstracted from the survey and can be reused for any survey.

 

[GrexP]

So what you're saying is tblAnswerType would look like this
1 - Poor-Good
2 - Rarely-Often

tblAnswers would look like this:
Type, Value, Text
1 - 1 - Very Poor
1 - 2 - Poor
1 - 3 - Ok
1 - 4 - Good
1 - 5 - Very Good
2 - 1 - Never
2 - 2 - Not Often
2 - 3 - Seldom
2 - 4 - Regularly
2 - 5 - All The Time

tblQuestions would be like this:
Type, Sequence, Text
1 - 1 - "How would you rate the service?"
1 - 2 -"How would you rate the CEO?
2 - 3 - "Do you ever tell others about us?"
2 - 4 - "Do you spit on the sidewalk?"

Maybe I'm missing something, but if that's the case it sort of seems like I don't need the tblAnswerType. If I join tblQuestions .Type on tblAnswers .Type it should give me what I want. This also seems to be my final suggestion in the original post.

 

[Banana]

You have the right idea.

While you could just join Question.Type to Answer.Type, how would you maintain the Type if you were to add a new Type? With the AnswerType table, you only add one Type once, and when you make the new set of Answers, you just select which Type it belongs to (and if you like, have it belong to more than one Type), then just tell what type a Question will use. It's basically normalizing the data.

But if this is like one-time thing then sure, go ahead. But never say "this will be all I will *ever* need". Next year, you'll be crying over that statement.

HTH.

 

[GrexP]

Not that I'm disagreeing with you, (honestly, I'm not) but I am struggling to see the subtleties. If I add a third type of answer then my data will look like this, for instance

tblAnswers would look like this:
Type, Value, Text
1 - 1 - Very Poor
1 - 2 - Poor
1 - 3 - Ok
1 - 4 - Good
1 - 5 - Very Good
2 - 1 - Never
2 - 2 - Not Often
2 - 3 - Seldom
2 - 4 - Regularly
2 - 5 - All The Time
3 - 1 - Blue
3 - 2 - Red
3 - 3 - Yellow
3 - 4 - Green

tblQuestions would be like this:
Type, Sequence, Text
1 - 1 - "How would you rate the service?"
1 - 2 -"How would you rate the CEO?
2 - 3 - "Do you ever tell others about us?"
2 - 4 - "Do you spit on the sidewalk?"
3 - 5 - "What is your favorite color?"

I still fail to see the benefit of the tblAnswerType.

 

[Banana]

That's okay.

Let's try something. Suppose we delete Type 2.

But we forget to delete the question using Type 2... What do you think will happen?

The thing is you're basically adding Type in *two* places (tblQuestion and tblAnswer), and it's entirely up to you to make sure everything is consistent. It's not too bad if you have a small set of data, but what if you have 100s of questions with several different answers? What if your boss comes along and ask you to change all the answers?

With a tblAnswerType, you change things only once, which will then propogate the changes to both tblQuestion and tblAnswer, and if you have relationship, Access will warn you if you're about to do something that will cause problem like deleting Type, leaving some questions with no Type at all.

The idea of database normalization is basically this: You want to store a data *once* and *in one place* only. You then use the key to reference the same data again and again; which is why we all use primary keys and foreign keys.

 

[GrexP]

Ok, I think I get it now.

tblQuestion and tblAnswer will have a Many to Many relationship. By adding tblAnswerType I will now have a One to Many relationship between all three tables with the "One" being tblAnswerType, and tblQuestion and tblAnswer being the "Many". By building these relationships in Access I can ensure retarential integrity between the tree tables.

If I understand it correctly, then in my original model, if I were to delete all type 2 answers I can orphan the type 2 questions and I will end up with questions that have no answers.

Is that it?

 

[Banana]

Yes, that's right.

Just a matter of semantic so we don't get confused; tblAnswer doeesn't actually represent the *actual* answer from the survey. A better name could be tblValidAnswer that tells you what answers are valid for a question. You would need another table to actually store the answers from survey.

Then yes, you can say there's a many-many relationship, and the Type help maintain the integrity.

Good luck!

 

[GrexP]

Yes, thank you for your time.