Confused on table structure for questionnaire and choosing between one to many and many to many for certain types of questions.

[Bettany]

I'm creating a questionnaire database. There are default questions, which will appear on every questionnaire, and then there are questions that are just created as needed and will not be replicated to each questionnaire. I need to be able to bring both sets of questionnaires questions together into a single form, so that users (the people who will be managing their questionnaire to ask customers the questions) can select the desired order of their questions. To my thinking, this breaks down into many-to-many tables (a questionnaire can have many default questions, and a default question can be a part of many questionnaires) for the default questions, and one-to-many tables for questionnaire specific questions (the questions that don't need to be available for other questionnaires). The form that will manage both these question types will be based on a temp table which copies in the default questions from the many to many junction table for the default question, as well as the one to many question-specific questions together into one continuous subform, so that questions can all be seen together and deleted, ordered, created, etc as needed.

Does this breakdown of question types into these table relationships as well as the general approach make sense?

 

[GaP42]

Suggest you look at some sample db. Ken Sheridan' has various sample db on a one drive. One of the files is Questionaire.zip.

OneDrive

Sign in to your OneDrive cloud storage and Office Online.

onedrive.live.com

Good starting point.

 

[plog]

Its hard, but the key to getting your table structure right is to put forms out of your mind. The data itself dictates table structure, nothing else. Forms are the last object you should work on in an Access database. Tables, queries, reports then forms. No point worrying about and working on forms that belong in the Louvre if they only end up dumping data into tables from which you can't retrieve anything meaningful.

The second red flag I saw was 'temp tables'. These are generally a hack around a poorly structured database. I don't really follow how you intend to use them, but everything about the data you described doesn't really need them if you set up the actual tables correctly.

For your specific instance, I think we need a better idea of the big picture. Give us one paragraph without any database jargon that tells us about the organization itself and what function this database will perform. Then another paragraph with limited database jargon about what data you want in it and how it will be used.

 

[Bettany]

We need to survey our contacts by asking them a series of questions and recording their answers. Some questions are standard and must be asked in every survey (default questions), while others are unique to specific surveys and are created as needed.

Default questions seem to follow a many-to-many relationship because a single default question can appear in multiple surveys, and a single survey can include multiple default questions.

Personalized questions, on the other hand, seem to follow a one-to-many relationship because these questions are unique to specific surveys and are not reused. For these, we will have a table for specific questions linked directly to the survey table, which includes the order of these questions.

Does it make sense to break up questions into different tables since they function differently?

So we need to store questions, both default and personalized, and the answers to each questionnaire give by our contacts. Default questions and personalized questions, while both forms of questions, seem to belong in different types of table relationships, since default questions seem to be governed by rules of many to many and personalized questions seem to be governed by rules of one to many. I have much of this database done already. But this design decision is the one piece holding everything else up. I'm using a temp table because I plan to base a form on that table, since I need a place to bring default and personalized questions together in one place so they can be ordered 1-10 (assuming there are 10) in the order the question-asker will ask the contact.

Thanks.

 

[The_Doc_Man]

The subject of questionnaires has occurred many times in this forum. You should be able to search for "questionnaire" and get dozens of hits.

As to the merging of different sets of questions, you can choose your questions and then use a UNION query to get them all in one list.

 

[GaP42]

Seems to me that generally you would have a "bank" of questions from which you can select any to be included and sequenced in a questionaire. The "client" created questions are just a type within the bank.

A partial possible schema that does NOT include an attribute to distinguish the source of the question is shown below:

QuestionIndex controls the order in which questions are presented in a questionnaire, Answer Index similarly for the possible answers to a question.

BTW - you need to distinguish between the answers that the respondents may select from to any given Question, and the Responses they actually select. (if choices are offered) - in the Session that they give the response (Do you need to accommodate the same respondent undertaking the questionnaire at a later date, to see for eg attitudinal change?).

 

[Bettany]

The subject of questionnaires has occurred many times in this forum. You should be able to search for "questionnaire" and get dozens of hits.

As to the merging of different sets of questions, you can choose your questions and then use a UNION query to get them all in one list.

Thanks for the info. I have everything else set up just fine, I just need to finalize the one design decision: split up default questions and questionnaire specific into two different tables or something else. I was under the impression that UNION queries are not editable. Once the two questions sets are together, I need for end users to be able to arrange both sets of questions (as one large conglomeration) into the desired order of questions to be asked; because a temp table is of course editable, I figured I could allow them to mark questions inactive, add new ones, set the order, and then save them back to them to a table with all that info.

 

[Gasman]

I would just have a field that defines if default or not, likely a boolean field?
A normal table is also editable?, so I would just allow the fields to be amended, sort order etc

 

[MajP]

For someone who has done a lot of survey design, I think properly structured, reusable survey is one of the more complicated relational designs. To do it correctly, normalized, and flexible takes a lot of thought. It is a great exercise for learning advanced relational principles. I see so many done wrong. They work for one survey and cannot be reused. Here is a good design IMO for a reuseable structure.

At Your Survey

At Your Survey (AYS) is a full featured application that allows users to create their own surveys...

www.rogersaccesslibrary.com

 

[Bettany]

I would just have a field that defines if default or not, likely a boolean field?
A normal table is also editable?, so I would just allow the fields to be amended, sort order etc

But the one to many vs many to many has important design and UI implications. If I go with many to many, all of their personalized questions have to go into a parent table, and they will have to select from saved questions rather than freely typing in whatever they want (otherwise, what would be the point of using many to many?). Selecting from a stored group of questions of IS the use case for default questions, but is not the case for specific ones. I don't want them to have to read over stored questions and pick from a selection, or have to confirm that the question they're writing is to be saved. I just want them to be able to type in what they want. Many to many implies selecting from a group of stored questions and typing in a new one if they can't find one that's similar to what they want. One to many implies they can type in the question they want and be done with it.

 

[Mike Krailo]

I just looked over that At Your Survey db and that thing is packed with excellent techniques. I like how he handled the 3 levels that could clearly be a part of any survey. Thanks for posting MajP.

 

[Pat Hartman]

split up default questions and questionnaire specific into two different tables or something else

All the questions belong in a single table. Do NOT use two tables. This will overly complicate everything else. If you want to have some questions that are always added to a new quesionnaire, then add a flag field to the masterQuestion table. When you create a new questionnaire, run an append query that copies all the "always" questions and add them to the new questionnaire. Then pick the variable questions one at a time. Use a group field to help with grouping questions and then a sequence number within the group.

If you want help with how to generate sequence numbers that you can rearrange, post back and I'll give you a sample.

I've given you a solution based on what you have told us so far about your questionnaire application. I would probably not use this solution in exactly this way were I building the application though. I still would use only ONE question table though. to do otherwise will just cause future problems and more complexity.

Post your schema before you go back to making forms and we'll help sort it out. You CANNOT build forms until your schema is sound.