Newbie needs help with survey design

[Buakaw]

Hello,
I'm trying to design a database that can record the responses of a survey. This survey has 24 questions in total. This is the first time I'm doing this so I have zero experience.

The first survey question already has me stumped....

Question 1:
Which two TV channels do you watch the most during the day? At what times do you watch these channels?

a) First Channel (can only pick one)
b) Day (can enter multiple combinations, e.g. [Mon-Wed], [Mon, Wed, Sat], [Thurs, Sun], etc.)
c) Time (can enter multiple combinations, e.g. 8-9am, 8-11am, 10-4pm, etc.)
d) Second Channel (can only pick one)
e) Day (can enter multiple combinations, e.g. [Mon-Wed], [Mon, Wed, Sat], [Thurs, Sun], etc.)
f) Time (can enter multiple combinations, e.g. 8-9am, 8-11am, 10-4pm, etc.)

I've attached what I have so far as a JPEG.

Problems:


1) What is the best way to store the questions? The survey has 24 questions in total, I know I need to use the "memo" datatype but have no idea where to store it so it's clean and neat.

2) I have thought to do the "Day" field this way. They can combine "Mon", "Tues"..."Sun" in any possible combination. But I do not know how to create a field in a table that can accept multiple choices. Any articles or demo videos on how to do this?

3) Same goes for "Time". I thought to break it up to 6-7am, 7-8am, etc. in discrete 1 hour increments, but I don't know how to allow the user to select multiple timeframes. For e.g. he can just select 7-8pm, or 7-10pm by selecting 7-8pm, 8-9pm and 9-10pm.

Any help would be appreciated. Thanks!

[jzwp11]

1) What is the best way to store the questions? The survey has 24 questions in total, I know I need to use the "memo" datatype but have no idea where to store it so it's clean and neat.

All of the questions should be records in a table

tblQuestions
-pkQuestionID primary key, autonumber
-txtQuestion (the actual text of the question)

In the above tblQuestions is the table name while the names preceeded by a hyphen are the field names in that table. I usually use some prefixes to help me identify the type of data that will be stored in the field. pk denotes primary key, txt=text, lng=long number, dte=date/time, fk foreign key which is a long number field that will link to the pk of another table.

If the number of characters in any 1 question exceeds 255 characters then you need a memo datatype field, otherwise a text datatype field will do. I assumed a text field would work in the table above, but you'll have to evaluate the length of each of your questions.

Which two TV channels do you watch the most during the day? At what times do you watch these channels?

The above would be 2 questions, so therefore 2 records in tblQuestions.

2) I have thought to do the "Day" field this way. They can combine "Mon", "Tues"..."Sun" in any possible combination. But I do not know how to create a field in a table that can accept multiple choices. Any articles or demo videos on how to do this?

Easy, you would not store separate selections within 1 field or even a series of fields, but rather as a series of records in a table. For example, let's say your question is this: Which days of the week do you watch channel 1?
Since a question has multiple possible responses, we need to store each response. In relational database jargon, we have a one (question)-to-many (responses) relationship which requires that the responses be stored as distinct records in a related table.

In Access 2007, Microsoft added a new feature called multivalue fields. There has been some discussions on other forums whether these multivalue fields represent a normalized table structure. They do have some limitations as well with respect to running update and append queries against them, so I have not used them.

Now you might be asking what is normalization. Normalization is basically the rules that govern how to handle data within a relational database like Access, so it would be best to get a better understanding of the concept because you will definitely need it when designing your database. Check out this site as a start.

So as to keep things simple, I would store the names of the days of the week in a table. So obviously, the following table will have 7 records.

tblDayNames
-pkDayNameID primary key, autonumber
-txtDayName

3) Same goes for "Time". I thought to break it up to 6-7am, 7-8am, etc. in discrete 1 hour increments, but I don't know how to allow the user to select multiple timeframes. For e.g. he can just select 7-8pm, or 7-10pm by selecting 7-8pm, 8-9pm and 9-10pm.

Just like selecting the days, the same would hold for the time intervals. Again I would set up a table to hold the time intervals of interest to you. Based on what you have posted so far, I think just storing the interval as text would be sufficient, that may change as I learn more about your application.

tblTimeIntervals
-pkTimeIntID primary key, autonumber
-txtTimeInterval

You can use both the day of the week table and the time interval tables to provide choices to the people responding to the questionnaire. You can show these choices via combo or list boxes on a form.

[Buakaw]

Thanks very much for the tips. I've attached what I have so far. Believe it or not I sat and stared at the screen for hours just to come up with this, it's laughable I know. My brain needs a lot of work still to think like a db designer...

I had a hard time figuring out which side of the relationship was 1 and which side was many, especially between tblQuestions and tblTimeSelection. Is there a simple heuristic or technique that one could use?

Assuming I've done the table correctly so far, I don't know how to proceed from here because tblQuestions should be a static table. Once I've entered in all the questions, it shouldn't need to be updated again I think? So thinking is that tblQuestions should serve as a kind of lookup table.

So each survey will contain one full set of tblQuestions records. I now need to be able to have many instances of tblQuestions, but I can't figure out how to do this.

I thought, perhaps add another foreign key into tblQuestions to track which survey the question belonged to. But that would mean that for every survey, one needs to re-enter all the questions again, and I thought this probably isn't a very good method.

And I can't use pkQuestionID as a fk in another table to track which "set" of question it is, because pkQuestionID is an identifier for each question, not each question set.

If I want tblQuestions to serve as a kind of lookup table, then I can't possibly store a variable to track which survey the questions belong to.

Any ideas?

[jzwp11]

I won't be able to look at your database until I get home tonight since I only have Access 2003 here at work, but I'll try to give some feed back on some of the issues you mentioned.

So each survey will contain one full set of tblQuestions records. I now need to be able to have many instances of tblQuestions, but I can't figure out how to do this.

I thought, perhaps add another foreign key into tblQuestions to track which survey the question belonged to. But that would mean that for every survey, one needs to re-enter all the questions again, and I thought this probably isn't a very good method.

If you have multiple surveys each with their own grouping of questions, that describes a many-to-many relationship. A survey can have many questions (one-to-many). I will assume that a particular question may show up in many surveys (one-to-many). When you have two one-to-many relationships between the same two tables, you need a juction table.

So a table to hold the surveys

tblSurveys
-pkSurveyID primary key, autonumber
-txtSurveyName

Of course we need the table that holds the questions

tblQuestions
-pkQuestionID primary key, autonumber
-txtQuestion (the actual text of the question)

Now the junction table:

tblSurveyQuestions
-pkSurveyQuesID primary key, autonumber
-fkSurveyID foreign key to tblSurveys
-fkQuestionID foreign key to tblQuestions
-longSeqNo

The longSeqNo is a long number datatype field that will hold a sequence number for the question in the survey in case you want to present the questions in a specific order

Assuming I've done the table correctly so far, I don't know how to proceed from here because tblQuestions should be a static table. Once I've entered in all the questions, it shouldn't need to be updated again I think? So thinking is that tblQuestions should serve as a kind of lookup table.

True, tblQuestions will function much like a lookup table. You will of course have the ability to add questions in the future as needed.

Going back to your questions, what types of responses others than selecting days/time as we previously discussed could you have? Do you have some questions that require a yes/no response, a short answer etc.? I am rethinking my suggestion of having two tables to hold the names of the days of the week and the time intervals. It will probably be better to have 1 table that holds all possible selection types of responses. Of course, with that we would need to categorize the questions as to what type of response is appropriate for that question, and likewise, categorize the responses as well. So, let's set up a table to hold the various categories

tblCategory
-pkCategoryID primary key, autonumber
-txtCategoryName

We'll also need to create a table to hold all possible responses

tblPossibleResponses
-pkPRespID primary key, autonumber
-txtPResponseName
-fkCategoryID foreign key to tblCategory

We would then just bring the data in from the tblDayNames and tblTimeIntervals, add the category and then we can delete tblDayNames and tblTimeIntervals.

We will also need to modify tblQuestions to add the category, that way we can match the question type to the possible responses.

tblQuestions
-pkQuestionID primary key, autonumber
-txtQuestion (the actual text of the question)
-fkCategoryID foreign key to tblCategory

Is there a simple heuristic or technique that one could use?

I saved the best for last... I don't think there is a simple way, you basically have to analyze your data and find the relationships. For example, you said this:

...tblQuestions to track which survey the question belonged to

This implied to me that you had multiple surveys and each had a different assemblage of questions. From that I could say that a survey had many question,s so survey would be the one side of the one-to-many relationship. Since you had many questions for a survey, the questions would be the many side. Now I made an assumption above that the same question can be found in many surveys and hence that is why I proposed the junction table. If a question was exclusive to 1 and only 1 survey, then you would not need the junction table.

[Buakaw]

I won't be able to look at your database until I get home tonight since I only have Access 2003 here at work, but I'll try to give some feed back on some of the issues you mentioned.

Ok thanks, I'll save it in 2003 format next time.

If you have multiple surveys each with their own grouping of questions, that describes a many-to-many relationship.

No, that's not what I meant. What I meant was that many people are going to take the same survey, and I need to track which survey was done by which person. The survey stays the same and the survey questions stay the same.

Currently I don't know how to do that with tblQuestions.

Going back to your questions, what types of responses others than selecting days/time as we previously discussed could you have? Do you have some questions that require a yes/no response, a short answer etc.?

Other than responses that can be looked up in a table (like time, day, what TV channels), here are some other kinds of questions:

- Ranking a list of criteria from most important to least important. E.g. rank the following factors in order of importance, each rank should only be used once. a) Price b) Environment c) Quality d) Customer service etc.

- Rating certain factors about the company. Rank from 1 - Very poor 3 - Average, 5 - Excellent. Criteria to rank could be things like a) Location b) Cleanliness of premises c) Flexibility in meeting your needs etc.

- Then there are general comment sections where the person doing the survey is free to write what he feels.

Thanks for your help. I need to run (to work) now, will study your response in more detail, thanks!

[jzwp11]

- Ranking a list of criteria from most important to least important. E.g. rank the following factors in order of importance, each rank should only be used once. a) Price b) Environment c) Quality d) Customer service etc.

- Rating certain factors about the company. Rank from 1 - Very poor 3 - Average, 5 - Excellent. Criteria to rank could be things like a) Location b) Cleanliness of premises c) Flexibility in meeting your needs etc.

I'm thinking that these choices can also be put into the tblPossibleResponses table as well.

No, that's not what I meant. What I meant was that many people are going to take the same survey, and I need to track which survey was done by which person. The survey stays the same and the survey questions stay the same.

Sorry for the misunderstanding. Actually, if there is a chance that you might offer different surveys (i.e. different groups of questions in a survey) then I would keep the structure I proposed. You can set up your one survey now and basically be ready for any future possibilities. I'll leave that call to you.



The following tables will not change:

tblCategory
-pkCategoryID primary key, autonumber
-txtCategoryName

tblPossibleResponses
-pkPRespID primary key, autonumber
-txtPResponseName
-fkCategoryID foreign key to tblCategory

tblQuestions
-pkQuestionID primary key, autonumber
-txtQuestion (the actual text of the question)
-fkCategoryID foreign key to tblCategory

Regarding tracking the actual answers provided by the various respondents, we have not gotten to that point yet. I've provided a few hints below; I'll let you see if you can come up with the additional tables you will need.

1. You will need some place to put info on the respondents.
2. Each respondent will have an many answers
3. Each answer relates to a question.
4. Each answer is to be selected from the list of possible responses.