CODEWITHSUNDEEP
databasedatabase-design
Miguel Stevens
Miguel Stevens

Reputation: 9191

Storing large sets of 'survey answers'

I'm building a database for a survey system.

We have employees that fill in a survey with categories that have questions. The employee can give him/herself a score (for example 6/10). Now we also have coaches which will fill out the same test for that employee.

My current database looks like this

+------------+------------+---------+-----------+
| EmployeeID | QuestionID | CoachID | Answer    |
+------------+------------+---------+-----------+
|          1 |         10 | null    | 5/10      |
|          2 |         11 | null    | 8/10      |
|          3 |         12 | null    | 6/10      |
|          1 |         10 | 1       | 5/10      |
|          2 |         11 | 1       | 8/10      |
|          3 |         12 | 1       | 6/10      |
+------------+------------+---------+-----------+

As you can see 1 employee, 1 coach and only 3 questions provide these rows. This will become big and I'm worried about this setup.

What if there's 40 employees, with each 2 coaches and 100+ questions.

Is there a better way to go about this?

JSON

I was thinking of using JSON fields, that store an array-like representation of the scores, such as

[3,4,5,7]

But the issue is I can't do queries on that field, right?

Looking forward to your way of handling such a database. Thank you!

Upvotes: 1

Views: 50

Answers (1)

Jo Douglass
Jo Douglass

Reputation: 2085

Relational databases can handle millions and even billions of rows - and this is a very narrow table (i.e. only a few columns). Even with thousands of different employees and questions this would not count as a big table. I would not be worried on that front.

If you want to reassure yourself of this, set up some test data. This is a really good practice, anyway - far too few developers try testing their system with a comparable amount of data to the amount that will exist after the system goes live. If you already know you'll have 40 employees, 2 coaches, and 100 questions then you know exactly what test data to set up. Create it and try out some queries matching those that will come from the front end. If you have performance problems, check things like indexes.

Going a step further, think about how much data will be in this system over its lifetime. If the employees will answer new questions each year then think about how many years this system will need to hold historic data for. If it's 5 years, then go with 40 staff members, 2 coaches, and 500 questions. Maybe the expectation is that the company will grow a lot, so to be on the safe side, you could try 100 staff members, 5 coaches, and 500 questions.

Setting up data of this kind can be a bit time consuming, but it'll save you a lot of pain if you find out these issues up front, rather than finding out you've chosen a poor data model or written some queries poorly after go live. And in your case the data model looks simple enough - and the amounts are small enough - that it probably won't really take you all that long to set up and test.

Upvotes: 2

Related Questions