As a web developer, I've typically only worked with nice, normalized tables in SQL-based RDBMS, but I'm doing some data consolidation for a researcher who will be analyzing correlations between all items of several very long questionnaires and wants a giant CSV with data for all takers and all items. So, say, 3000 rows and 5000 columns (?).

I set up tables for each questionnaire (each has its own CSV), but we're talking tables with 1000+ columns representing item responses for each taker / pkid. Obviously I can't join up four, five different questionnaires like that based on pkid -- I'd have 5K or more columns and I'm already at the outer limits of MySQL just on the individual tables.

So do I: 1) look into NoSQL at last, 2) just programmatically write a one-off script to consolidate this data 3) convince scientist to buy Oracle or MS SQL 4) other option I as a non-data-scientist don't know about?

Thanks for the advice!


  • Or, should I just join data frames in R? – Joy Feb 18 '15 at 16:30
  • 1
    Why not just pivot the data so each row contains questionID, takerID, answer with taker and question tables holding the remainder of the data? Normalizing your data is ideal. – LowlyDBA Feb 18 '15 at 16:59
  • 1
    @JohnM it can be a bit more complicated if they want to have separate types for answers on different questions (Yes/No, Multiple Choice, Numeric, free Text, Dates, etc) But I agree, if they want to use a RDBMS, normalizing should be the road to follow. – ypercubeᵀᴹ Feb 18 '15 at 23:48
  • There are 5000 possible answers per questionnaire? I'm guessing some are free-form text responses, yes? – Neil McGuigan Apr 16 '15 at 17:11

1) Consider how this information will be used. Will the owner want to try lots of ad hoc analysis and data exploration? Does your potential data store have tools or connection libraries that allow this? Will the user have to resort to writing Map and Reduce functions for every question posed?

2) Do you mean that the same question is on different questionaires and you're wondering if you should recognise they're the same in your data store design? Yes, absolutely you should, whatever thechnology you end up with.

3) The DBMSs have column limits, too. They may be larger, but they're not infinite.

4) Store your data as one row per answer. There are several qustions here on quiz design. Here's one picked at random. These will give you pointers and any detailed questions remaining will be good material for future DBA.SE posts.

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.