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
- 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 => 1, B => 2)
WHEREclauses will you use with those aggregation queries?