Topic: Best way to structure a database for a large/static dataset
I’m a rails newbie and am looking to create an app to work off a very large dataset. The dataset contains survey data from 1980 through 2011 for about 8,000 respondents. Each year’s survey has between 900 and 7000 variable associated with it.
Basically, the app will be used to view all information for one respondent (seeing all the years at a glance), compare respondents, etc.
It seems like creating a table for each year isn’t necessarily the best strategy. What would be a good way to structure this data for the database? I’ve considered just having a table that’s basically the respondent ID, survey year, variable name, and value name. The difficulty there is that the values would range from simple boolean fields to fields with extensive amounts of text, so standardizing that field would be tricky (and probably very inefficient).
Any thoughts/tips/resources on where to turn?
Thanks in advance.