I'm looking for advice on a design for a portion of a table.
We're collecting survey data for multiple sites, and one question is a
check all that apply. Currently, there are from 2-5 available options based on the site, though that number could increase. When I get the data, I get anywhere from 2-5 columns (based on the number of options available on that survey), each column in the result set responds to one of the options. The data in the columns will all be either
NULL or a text value representing that ordinal option for that particular survey. Different sites will also have different options from a set, so there is no guarantee that column 1 is always the same response. There are 13 total possible options to pick, at this point, and no one site has more than 5 available, but it's possible that we'll add more options to pick from, and that the there could be more than 5 options per site in the future.
Col 1 Col 2 Col 3 A B B A C A B C
Col 1 Col 2 Col 3 B D D E E B
Col 1 Col 2 Col 3 A D D F A F A
How do I best store this data?
I get the data in a .CSV, and for expediency & urgent deadlines, I've been pasting it into an Excel spreadsheet. (That's why I'm looking to move it to an actual table...) I've created one column for each possible answer, and if the value in
CSV.Col1 = Excel.TableHeader, then I put a 1 in that column. This allows me to quickly filter the Excel rows and sum the
1s for a quick total of how many there were. This is probably not ideal for an actual database.
- I've considered assigning each of the 13 options a value and summing them to store them in a single column. For Example:
- A = 1
- B = 2
- C = 4
- D = 8
Survey 1example from above would store the values of:
- I've considered leaving it as it is currently with 13 columns of either Boolean, Number, or Text format. Each column would hold
[A|B|C|D...], as appropriate.
- Creating a
1:Mdependent table to store a result per row, linked to the master table row for that survey. The child table would have a
Foreign Keyback to the parent table, and a
textcolumn to hold the response.
The back end
The data will be moved from Excel to Access, with the intent to move it into SQL Server as soon as possible.