Help needed on table design in database

[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