2 Replies Latest reply on Feb 3, 2012 7:56 AM by Tom Hier

    Proper structuring of databases for survey research

    Tom Hier

      I need some basic help in determining how best to structure databases for Tableau when I do survey research.  I know abou the "reshaper" but I'd like to understand more about what makes a good database structure in the first place.  Can anyone help me out?

        • 1. Re: Proper structuring of databases for survey research
          Russell Christopher

          Wow - this is a great question..but also a hard one to answer.

           

          Based on what you mentioned in the question itself (data re-shaper) I'm guessing you have a pretty good handle on the basics: Generally, you're going to have a hard time with any data structure that "looks like a spreadsheet".

           

          What do I mean by this? How about a Month-Year (like 1/2012, 2/2012) on each column in a workbook. Or in your case, distinct survey questions as each column header with  a count of the "answers" for that question in each cell under that column. This sort of design will generally give you a really "wide" table (lots of columns), and will almost always need to be "pivoted" with something like the data re-shaper before you can do any real work with it.

           

          In general, any collection of "like items" (survey questions, dates, product colors) should be placed in a single column vs. "spread out" across multiple columns,

           

          As a guy who supported Microsoft Access many, many years ago, I found that the article below (which has been updated many, many times!) does a good job of talking about the basics. It happens to focus on the Microsoft Access "features" a bit, but you can ignore that stuff:

           

          http://office.microsoft.com/en-us/access-help/database-design-basics-HA001224247.aspx

           

          If you have any specific questions afterwards feel free to post them here - but your question is pretty broad, so I hesitate to attempt a broad answer as it could take pages and pages - I'd put you to sleep!

           

          Cheers!

           

          Russell

          1 of 1 people found this helpful
          • 2. Re: Proper structuring of databases for survey research
            Tom Hier

            Russell,

             

            Thank you for the link.  I'm looking forward to reading it -- at a quick glance, it looks like just the type of resource I was seeking.  Indeed my question was very broad, because I'm trying to get a big conceptual handle on database structuring.  What led to the question, specifically, was trying to work with some survey data that I have.  I've figured out how to set up the data for an individual question (e.g., taking all of the "cross-tab" fields -- age, gender, etc -- and adding them to the specific data for the question), but I'm having a hard time believing that I have to set up a separate database for each question.  There must be some way to take the overall survey dataset and structure it as one grand database to use in Tableau.  That is my ultimate mission.

             

            Again, thanks for the link.  I'm hoping that will provide some answers.

             

            Regards,

            Tom