Help needed with table design please!!!

[Darren Bartrup]

Ok, I'm trying to envision a database that I need to build but it's making my head go into meltdown

Each year my department gets a number of completed questionnaires sent to us and I need to build a report based on the contents of these questionnaires. It would also be nice to compare this years results to last years results, or the last three years worth of results, etc...

Sounds simple enough... but:

- The questionnaires are returned from various councils, and the numbers returned may vary year on year (this year we had 27 returned).

- The questions are divided into sections, which may (or may not) contain sub-sections.

- The questions may also be divided into sub-questions.

- The answers are mainly numbers, but may be Yes/No, free text or an option/combo box answer.

- Question number 1 this year might be question number 5 next year and it may be in a different section.

- The number of questions from year to year may vary.

- I also need to know which questions I can compare against each other, e.g If one question is a total and three other questions are a breakdown of the total then I need to be able to identify this. I also need to know if a number can be broken down by a population count - e.g can I break 12261 down to per 10,000 population aged 18-64?

I'll need to draw an E-R diagram for this just so I can follow it but I'm having trouble just getting my head around it.

I'm thinking of a table holding Council_id(primary key), council_name.
Another holding year_number, question_number, section_number, parent_question, parent_section
Another holding question_number, question_text
And another holding question_number, council_id, question_result

Please, any ideas welcome on getting the theory of this database built!

Hope I've explained it ok

 

[NumbersMax]

Here's an opinion:
1) Normalization is key
2) The volume of questionaires returned should be immaterial
3) As far as tables go:
a) Council # and Council Name in 1 table sounds good
b) Question table containing an autonumber field & question text.
c) Sub-question table containing an autonumber field & question text. If you have questions that can be a parent or sub question, it could be loaded in each table.
d) Build a questionaire table by picking questions from a form that linked back to the various tables described above. Use that table to print out your questionaire in a report format for distribution. This table would have the pertinant info like: Year (can be hidden on the report), Council#, Survey question # (number field), Question #, Sub-question # (0 is not a subquestion), text field for answer
e) Main table to store results that contains most of the necessary fields, such as Council #, year, SurveyQuestion#, question #, sub-question#, answer field.

You can build/distribute the survey based on the questionaire table. Then when the questionaires come back, fill in the answer in the same table and append to the main table for your records, maybe?

The reason I say test for your answer is that text can be changed to yes/no, number, etc with the right formula, and then analyzed, but you can't put text in a number field. This may not be the best answer!

I also need to know if a number can be broken down by a population count - e.g can I break 12261 down to per 10,000 population aged 18-64?

I'm not sure what you mean here.

I also need to know which questions I can compare against each other, e.g If one question is a total and three other questions are a breakdown of the total then I need to be able to identify this.

A field could be added to flag indicating if it is a parent answer or a child. The question/subquestion fields could help with with this.

Hope this gives you ideas!
Max