Which DB for 1 table with 100 Million rows per year? I've a questionnaire with 70 questions and need to store the answers.

Problem: it's 100 Million records per year.

I've experience with different types of storages, but never had to deal with these huge numbers. Now I'm afraid that every wrong decision might lead to big negative impact.

Information about the data:


I was thinking about 1 table with 70 columns
The columns are already defined and might be slightly adjusted after a while (+/- 10 columns)
The data type of each column is mainly Integer and String with mostly 2 chars, max. 10 chars.
No nested (tree) structures needed
No flexible data types needed
No joins needed


Data definition (pseudo-code)

COLUMN           |   TYPE   | MAX. LENGTH
-----------------------------------------
id               | Integer  | 10  
questionnaire_id | Integer  | 10
answered_at      | Datetime | -
answered_by      | Integer  | 10
answer1          | Integer  | 2
answer2          | Integer  | 2
answer3          | Integer  | 2
answer4          | Integer  | 2
...
answer35         | String   | 2
answer36         | String   | 2
...
answer70         | String   | 2


Priorities:


Storing big data
Run standard aggregate functions (avg, min, max, count, ...), filter and sort in an acceptable time


Is there any best practice or a checklist to follow which reduces my options and therefore wrong decisions? 

Thank you in advance!

Edit: normalized, inspired by Dave

# questionnaire
- id (PK, AI)

# questions
- id (PK, AI)
- questionnaire_id (FK)
- label

# submits
- id (PK, AI)
- questionnaire_id (FK)
- answered_by
- answered_at

# answers
- id (PK, AI)
- submit_id (FK)
- question_id (FK)
- value      // Integers only (strings are mapped: A =&gt; 1, B =&gt; 2)
With that table an no extra keys/indexes you have 160 bytes per row, so 100,000,000 rows per year is about 16GByte per year. Any decent DBMS (SQL Server, postgres, [YourFavouriteDBHere], ...) should be able to cope with that and (assuming proper indexing) query it efficiently if you have appropriate hardware/virtual resources. The extra space taken by keys and other indexes shouldn't balloon the space requirements much, if it does then it is likely that the structure is not optimal more generally.

So simply storing the data should not be a worry.

Some databases support compression, sparse tables, and other techniques, that will help considerably with this structure if space is your primary concern, but before considering them first ensure that this is in fact the structure that you need.

As others discuss in the comments, your current structure may not be optimal for the analysis that you need to perform, so you would need to edit your question to include such details if you need help there. A key thing in all database design is to consider your desired outputs as well as your inputs.


  Unfortunately I don't know every required select-query at the moment.


You must have some idea of the sort of reports you expect to run against the data. It doesn't need to be every query you'll ever run, or even close to, but optimising for some expected reports is far better than blindly ploughing in data in the hope that it might be optimal for something/anything that will eventually come up.

Where at all possible, do not design based purely on your inputs.

Without any idea of the outputs, perhaps yes: just throw the data into a table like that with indexes on the date at least as that is very likely to be one of the key ways you'll want to filter/partition/aggregate the data in reports, and transform the data into something else via ETL when you do know what analysis you need to perform. But if you have an idea of the outputs to start with, you might be able to avoid having to create and maintain two structures (one for active recording and one for reporting) and the process to transform data from one into the other. Of course, this two structure system might be optimal, but we simply can't tell you one way or the other without more detail.