Table design Questionnaire/Survey app (lengthy)
I can't seem to get my head around designing all the tables for this app and need some ideas.
Following are portions from the existing document.
Code:
D. Section Heading
Sub Section Heading
Pre Post
Date of Administration ______ ______
1. SSI
1. # of Positives ___ ___
2. # of Negatives ___ ___
3. Positives - Negatives ___ ___
Final SSI score ___ ___
2. Deficits
... 6 questions in this section
...5 sections total.
I am not sure if I should denormalize and store each Pre/Post question separately. Certainly in the Answer table I would need to somehow do this in order to satisfy the following query not to mention re-displaying the Answer in the correct location on the document.
Find all documents where Post # of Positives < Pre # of Positives
But another section has questions which repeat, under multiple headings as follows
Code:
B. Section Name
1. Current Items
Item Description Item Code Item Code B
_____________________ ____ _________
_____________________ ____ _________
_____________________ ____ _________
2. Previous Items
Item Description Item Code Item Code B
_____________________ ____ _________
_____________________ ____ _________
_____________________ ____ _________
By denormalizing, I would then need to store the above questions as
current item # 1 Item Description
current item # 1 Item Code
current item # 2 Item Description
etc...
Which I don't think is appropriate.
Also, there is "Short form" of this document which asks the above question in this format. It does not prompt for Item Code B.
Code:
Item Code Item Description
code X ________________________
code Y ________________________
code Z ________________________
Another section has this layout.
Code:
Person 1 Person 2 Person 3
1. Question
Choice 1 ...Choice N __ __ __
2. Question
Choice 1 ...Choice N __ __ __
3. Question ______ ______ ______
But the exact same questions are asked under a different heading which basically affects the meaning of the question. Eg. Instead of asking about a Person, the questions are asking about a "Thing" or a "Place".
I think I am confusing myself as to what belongs in "Document Layout" vs actual "Questions"
I just realized I am just rambling on and haven't really asked a question - my head hurts.