Questionnaire DB Design 1

[Elysium]

To anyone:

I have searched on this subject, but nothing addresses one of my main problems in designing a database structure for questionnaires. Here's my situation:

I have a questionnaire where each question has a different data type and a different number of question elements. Even further, a single question could be comprised of checkboxes, radio options, textboxes, and memo fields. For example, Question #1 is just a standard "Yes/No" radio option group, Question #2 has 7 checkboxes and one memo field, etc.

So, what is the most data-efficient way to design this type of database?

Elysium

 

[SQLSister]

Each element of a question which requires different storage of data would be a separate field with the appropriate data type. For instance if question 2 had a yes/no question and then a comments box after it (on the nature of Have you ever been convicted of a crime, if yes, explain.). then you would have two fields for question two, Q2A and Q2B. The first would be a bit field, the second would be a char or text field (depending on the max length of the answer). Then you can implement validation on your form which will not accept the data unless the comments box is filled out if the answwer to the first question is yes. That will keep your data integrity. Depending on the length of your questionaire, you may need to split into more that one table even if you have a one to one relationship between the tables becasue a long questionaire with a a lot of lengthy comments fieds could exceed the record size for a record. I'd also use varchar data types rather than plain char because it will only store the number of characters typed whereas the char types will take up the whole size you give them. I say this becasue your answers are likely to vary widely in length in the scenario described above.

Also, if you have questions in which multiple answers are simultaneously accepted, you will want to split each of these out to a separate table becasue they will have a one to many realtionship with the main table.

Hope this helps.

Judy

 

[Elysium]

Judy,

Thank you for your response. We were heading in the right direction then, based upon your recommendation. Now one more question, your last statement indicated that if a question had multiple answers that could be simultaneously accepted that I should use multiple tables. Does this apply to a question with several checkboxes that don't impose a mutually exclusive restriction. So, each checkbox answer would reside in a separate table?

RS

 

[SQLSister]

Yes, this is because you have a one to many relationship between your answers and the record that for a particular questionaire. For instance, we have a data entry form for airport businesses to fill out and in one section they can check all the services that they provide. The main part of the form (busines name, address, etc) are in one table and the services are in a separate table because a business might have one service or it maight have 25. No sense in repeating the data like name more than once. You then link the tables to see the complete information. So you want to create a QuestionaireID number field in the main table and then add that field to any related tables you need for questions with multiple answers.

As a personal prefernece, I also tend to take items where there is a list of choices and create a lookup table with a related integer ID field and the text description (in my scenario above it was ServiceID and Service Description). I use the lookup table only when I want to diplay the actual words, but store the information in the data tables using the ID field. This saves space and tends to make things run faster as the system can generally process faster on integer fields than text fields. I use these lookup tables as the source for any combo box lists, too. Makes keeping them up-to-date a snap, I only have to add or delete values in one place. If I use a lookup table I have a naming convention where I add lkp (for lookup) to the end of the table name. This helps me identify the tables where I need to keep the data between development and production in synch.

 

[VogonPoet]

Something to consider in designing your database is how stable is your questionnaire? If the questionnaire may change over time, you may want to think about a different table design with the following fields:

QuestionnaireID
QuestionNo
DataType
Value

The QuestionnaireID is the ID for the entire questionnaire
The QuestionNo is the individual question ID (2, 2B, etc.)
The DataType is the type of data expected for the value (bit, integer, etc)
All the Values are stored as text

Obviously this format requires more up front work to load the data and create any reports, but it allows you a lot more flexibility of the questionnaire changes (or if you want to use the same table to store info from different questionnaires).

 

[SQLSister]

An interesting suggestion, Vogon Poet. I can see where this could work but the programming to get the reports, to do the data entry, and validation would be more difficult. However, it would get away from the possibility of running out of room in the record from having too many fields. I also like the reusability for other questionnaires. I would add a few wrinkles however. It needs an ID field for the person filling out the questionnaire. This way you can store multiple responses and know which respondent each question answer belonged to. Doesn't have to be a name as many questionaires are anonymous, but it would help in checking the data entry if you could call up all the responses froma particular questionnaire and help in relating the answers to the personal data information, (i.e., how many single males answered the question this way).
Also, It would be a good idea to have a related table with the questionaire number, question number and the text of the question. This will also help in creating reports later.