Advice on database design for complex questionnaire

[mdb4me]

Hi,

I'm new to the forum and I'm looking for advice on a better way to setup tables and a form to capture data from filled out paper questionnaires.

The questionnaire consists of 30 questions but each question has 6 columns adjacent to it, eg.
*******************Mother************Father
Question No.*****Resp. | Period | Age|| Resp. | Period | Age
Q1.

The person filling out the questionnaire is required to answer questions in relation to their parents and depending on the responses, Period and Age columns maybe populated as well.

The person who previously setup databases for these type of questionnaires used the flatfile approach of creating a huge number of columns within one table eg. 30 x 6 = 180 columns and used a bound form with a vast matrix of textboxes to enter the data.

Anyway I'd appreciate any advice on a better approach.

thanks

 

[Rabbie]

I would advise you to search this forum for normalisation/normalization. Also to google for this info. Tables in a relational database like Access should be tall and thin without repeating fields.

Flatfiles are not usually the way to go in database design.

The information about father/mother needs only to be stored once for each respondent and not for each question.

Without more knowledge of your business model it is not easy to give you more specific advice.

 

[The_Doc_Man]

Not only should you read up on Database Normalization per Rabbie, but this forum has had a few "Questionnaire" topics as well. Search for that keyword in the titles of various forum articles, all categories.

 

[mdb4me]

Hi,

Thanks for responses, I'll have a search for previous threads and see what I can find out.
I recently commenced working at this health related research organization and they haven't had anyone with database expertise so the databases they have setup for thier research aren't normalized or relational in anyway (many of them were setup by research students with very limited Access skills).

They have myriads of questionnaires ranging from simple to very complex, and whilst I have been working with databases for quite awhile I've had no experience with surveys or questionnaires. So i'm unsure whether there is a standard model that can be applied for each research study or they may require different approaches.

 

[The_Doc_Man]

So i'm unsure whether there is a standard model that can be applied for each research study or they may require different approaches.

You raise a significant point here. Sadly, the nature of the database will very strongly depend on the style of the questionnaire, which pretty well boils down to "limited or no standards." It is a deep truth in database design that in a well-designed system, the problem rules the database, not the other way around. Therefore, the style of the questionnaire will be the most important aspect in your problem.

Notice I didn't say the questions were most important. They are not. It is the style of question and its possible answers that will get to you every time.

Which is why I suggest searching the forum. Several different styles have been discussed. One of them might appeal to you. If so, you can read over it and ask more strongly directed questions.

 

[Lightwave]

Mdb4me

Just in case you are not aware

There are online opportunities to conduct and collect information for questionnaires the most well known in the UK being survey monkey (US company I beieve)

I have in the past used Access but the data entry was tedious as was getting the questionnaires distributed.

The individual sends a web link to an online questionnaire and then lets the online database engine collate the information. You can then get a downloadable file which I then tend to import into access and use this as an analysis tool for producing reports rather than a pretty front end thing. The underlying data structure of my information has always been very simple. Completly flat no relationships and static.

Apologies if you here this all the time!

In which case all above are more relevant