Normalization Of Questionnaire

[CJBIRKIN]

Hello

I have a questionnaire that needs to be converted to a DB. My problem is i can see various solutions but if i get the normalization wrong at the start i'm going to be up the proverbial creek later.

The questionnaire is comprised of 7 sub questionnaires, not all the recipients will be expected to fill out all of them. i.e one person may only find they need to fill out part 1: Security and Confidentiality for example.

Each question is given 4 tick boxes YES, NO, N/K(not sure) and N/A (not applicable). There is also a comments field. The recipient will also need to attach supporting documention for each question they answer yes to.


I reckon that that the stucture should be:

TBL_QUESTIONNAIRE
SUB_QUESTIONNAIRE_Number; (PK)
Sub_QUESTIONNAIRE_Description

TBL_QUESTION
Question_ID; (PK)
SUB_QUESTIONNAIRE_Number; (FK from TBL_QUESTIONNAIRE)


TBl_ANSWER
Answer_ID; (PK)
Staff_Member_ID; (FK from TBL_STAFF)
Question_ID; (FK from TBL_QUESTION)
YES:
NO:
N/K:
N/A:
Comments
Document (Hyperlink)

TBL_STAFF:
Staff_ID; (PK)
First_Name
Last_Name
Phone_Number
Location_ID; (FK from TBL_LOCATION)

TBL_LOCATION:
Location_ID;(PK)
Room
Building; (FK from TBL_BUILDING)

TBL_BUILDING
Building;(PK)
Description
Site; (FK from TBL_SITE)

TBL_SITE
SITE_ID; (PK)
Description


I cannot change the structure of the questionnaire

The idea is that when we select a particular question we should be able to find all the staff who answered the question and the supporting documentation.

Can any one confirm or deny my analysis??


Cheers

Chris

PS how about a board for this sort of question (or should this be in tables?)

 

[RichMorrison]

Chris,

Offhand and without any deep analysis, I would not make the "sub" questionnaire number part of a table structure.

Even though different people will answer differents subsets of the question domain, your need for analysis capability based on a specific question means you will want to search the whole question domain and not questionnaire-based subsets.

So each question just has a QuestionNum as primary key. You can use a junction table to group questions into subsets to present a subset group on a form for data entry.

As time goes by, using a junction table will also make it easier to add questions and move questions from one group to another.

Second comment: you could make the PK of tbl_Answer the concatenated Question_ID and Staff_ID. That should produce a unique key combination. In other words, tbl_Answer does not need a distinct Answer_ID for a PK.

HTH,
RichM

 

[The_Doc_Man]

TBl_ANSWER
Answer_ID; (PK)
Staff_Member_ID; (FK from TBL_STAFF)
Question_ID; (FK from TBL_QUESTION)
YES:
NO:
N/K:
N/A:
Comments
Document (Hyperlink)

The YES, NO, NK, NA cases should be a single field for which you perhaps have a small text area 3 characters long. Then when you want to do analysis, you can do cross-tab queries over the answers to get counts of each answer as your columns.

I would take it this way:

tblQuestion
-- lngQuesNumber (PK)
-- strQuestion

tblQuestionnaire
-- lngQsnairNum (PK)
-- strDescription

tbl QuesQsnair
-- lngQuesNumber (FK) 'this question appears on ....
-- lngQsnairNum (FK) 'this questionnaire

tblAnswers
--lngWhosit (PK element 1) 'unique ID to show who answered
--lngQuesNumber (PK element 2) 'the question
--lngQsnairNum (FK)
--strTheAnswer

All of your other Staff-related tables probably contribute to the field I am calling lngWhosit, the field that uniquely identifies one person's response. It is immaterial to this layout whether you know the person to whom lngWhosit applies as long as the number is unique.

With this layout, you know who answered the questionnaire, what questionnaire they answered, and all of the answers they supplied. If you run into a blank answer, you use tblQuesQsnair to distinguish between "Not Answered" and "Not Asked" - assuming that makes a difference in this context. (You said that it did.)

 

[CJBIRKIN]

Hello

Thanks for your suggestions. Doc i take your point about the yes no... fields.

The idea for a questionnaire subset is simply to allow us to analyse that particular area individually. i.e Sercurity and confidentiality.

It will allow us to build a departmental map by topic to see which areas have control of major policy groups and any areas that may contain policies that are in effect outliers to them. This would allow us to restructure the policy holders more effectively.

uhmm, i love the smell of control freakery in the morning!

Thanks Chris