Database structure - best fit for recording lists
Posted on 2013-12-11
Odd question but my brain is melting over this one:
I have a form (on an iPhone) that returns answers to a number (about 10) of questions that will mostly return yes or no answers (although not exclusively so will be saved as a string) in an xml format. I expect about 20 users to complete the form every day and the forms may change over time and more forms may be added.
I want to store the data returned and have parsed the xml to extract the relevant information.
Should I create a tables to store that information like this (option 1) -
master table ID
question (in the form of a short code)
OR like this (option 2) -
Master table ID
question 1 (in a short name form)
question 2 (in a short name form)
and repeat for each question/answer
I think option 1 as querying to find which fields have answered yes would be quick but the number of rows will grow to a large number very quickly
Option 2 will give less records but queries will be harder etc.
Thanks in advance,