4

I'm looking for advice on a design for a portion of a table.

Background

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.

Sample Data

Survey 1:

Col 1       Col 2     Col 3
A           B
            B
A                     C
A           B         C

Survey 2:

Col 1       Col 2     Col 3
B           D
            D         E
                      E
B                         

Survey 3:

Col 1       Col 2     Col 3
A           D
            D         F
A                     F
A                           

The question

How do I best store this data?

Currently

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.

Possibilities Considered

  1. 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
      So my Survey 1 example from above would store the values of:
    • 3
    • 2
    • 5
    • 6
  2. I've considered leaving it as it is currently with 13 columns of either Boolean, Number, or Text format. Each column would hold [True|False], [1|0], or [A|B|C|D...], as appropriate.
  3. Creating a 1:M dependent table to store a result per row, linked to the master table row for that survey. The child table would have a PK, a Foreign Key back to the parent table, and a text column 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.

3

Option 3 is classic 3NF design. If you need to add data to the table quickly, it will likely be the highest performing approach for inserts, so if you need to insert the data quickly and have high concurrency concerns that may be the way to go.

To me, it sounds like you're trying to solve an analytical problem. If that is the case, I'd go with option 2. This is a de-normalized approach. Your users are likely already thinking about their data in that way, so they'll appreciate the simplicity in it. With the way that you're receiving your data, it could be the most difficult to achieve, but the effort to get it in simply will be worth it. Your users will appreciate the elegance which is hopefully your goal.

Good Luck!

  • Thanks for the input. For the foreseeable future, I'm the user, so that's not a big deal. As both you and Chris suggest, #3 does seem like the best method, and I'll probably go with it. Frankly, I was a bit stuck in the flat-file Excel mind-set, and that didn't even occur to me until I was typing up the question. – FreeMan May 15 '15 at 22:47
  • If you're building it for yourself, do both option 3 and 2. Both have their positive qualities and as you use them both you'll learn and better understand for the future. Best of luck! – Mark Iannucci May 18 '15 at 14:58
  • If I ever reach the point where other users are querying the data on their own, I might create a de-normalized table for them to retrieve the data from, but doing it up front seems like unnecessary extra work for myself. – FreeMan May 18 '15 at 15:20
1

Option 1 is doable, but not very user friendly and will eat at your CPU when it deciphers your values. Option 3, with properly designed indexes would be your best option.

  • Thanks for pointing out the challenge with #1, Chris. In my head, I'm thinking if Table.column AND 8 then, but I don't think VBA can do that, so you're right - that would be an awkward construct. – FreeMan May 15 '15 at 22:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.