I have a form that is being filled by customers, the form is very long and contains approximately 30-40 fields.
One part of the form is particularly complex to store as it is a matrix of checkboxes that the user needs to select.
Example:
> Fir Item | A Checkbox | B Checkbox | C Checkbox
> Sec Item | A Checkbox | B Checkbox | C Checkbox
> Thi Item | A Checkbox | B Checkbox | C Checkbox
> Fou Item | A Checkbox | B Checkbox | C Checkbox
> Fif Item | A Checkbox | B Checkbox | C Checkbox
What is the most efficient way of storing this data?
Thanks,
If all items contains same options, this should be your first choice:
[Customer] (Id, ....)
[Item] (Id, Name)
[Option] (Id, Name)
[CustomerResponse] (CustomerId, ItemId, OptionId)
If options "never" changes and you don't want to keep them in a separate table:
[Customer] (Id, ....)
[Item] (Id, Name)
[CustomerResponse] (CustomerId, ItemId, OptionA bit, OptionB bit, OptionC bit)