0

Background: Think of an application that lets people make surveys with custom questions, In a particular case, interview families, An interviewer goes to House 1 and interviews two members Member 1 and Member 2. He asks questions like. What is this house address?, What is your name? and What is your age?. The questions are created by supervisors or survey experts

The way the data is stored for this application is as given below

The questions are saved in a table like below
+------------+--------------+----------+
| QUESTIONID | QUESTIONTEXT | SURVEYID |
+------------+--------------+----------+
|            |              |          |
| 1          | Name         | 311      |
|            |              |          |
| 2          | Gender       | 311      |
|            |              |          |
| 3          | Age          | 311      |
+------------+--------------+----------+
Results table (It has all the submissions)
+----------+----------+----------------+
| RESULTID | SURVEYID | SUBMITTED DATE |
+----------+----------+----------------+
|          |          |                |
| 90       | 311      | 11/12/2017     |
|          |          |                |
| 91       | 311      | 11/13/2017     |
+----------+----------+----------------+
Chunks table (where all the answers are stored)
+---------+----------+------------+----------+
| CHUNKID | RESULTID | QUESTIONID |  ANSWER  |
+---------+----------+------------+----------+
|         |          |            |          |
| 1001    | 90       | 1          | John Doe |
|         |          |            |          |
| 1002    | 90       | 2          | M        |
|         |          |            |          |
| 1003    | 90       | 3          | 18       |
|         |          |            |          |
| 1004    | 91       | 1          | Jane Doe |
|         |          |            |          |
| 1005    | 91       | 2          | F        |
|         |          |            |          |
| 1006    | 91       | 3          | 26       |
+---------+----------+------------+----------+

And the result after some Joins and Pivots will be like

| RESULTID | SUBMITTED DATE |   NAME   | GENDER | AGE |
+----------+----------------+----------+--------+-----+
|          |                |          |        |     |
| 90       | 11/12/2017     | John Doe | M      | 18  |
|          |                |          |        |     |
| 91       | 11/13/2017     | Jane Doe | F      | 26  |
+----------+----------------+----------+--------+-----+

Now the problem arises when we have really big surveys ( Some of them may go as far as 500 questions ). Normally the submissions exceed 20k. With this huge data, it takes forever to reach the final result.

So the solution I have in my mind is to generate a table dynamically when a survey is created. like

Survey_311


+----------+----------------+----------+-----+-----+
| RESULTID | SUBMITTED DATE |   Q_1    | Q_2 | Q_3 |
+----------+----------------+----------+-----+-----+
|          |                |          |     |     |
| 90       | 11/12/2017     | John Doe | M   | 18  |
|          |                |          |     |     |
| 91       | 11/13/2017     | Jane Doe | F   | 26  |
+----------+----------------+----------+-----+-----+

And to insert rows each time an answer is submitted. But as I am not really a DBA and do not have much experience with Databases. I would like to know if this approach is a bad practice in this scenario, Any solutions other than paging that will improve the performance is also welcomed

  • 1
    Generally speaking it is not advisable to be constantly mucking with creating/altering tables as part of your normal workload. I personally wouldn't use this approach. 20k records is actually a very small amount of data for a database and I wonder what you're doing that "takes forever" exactly. Its also unclear what you mean by "paging" – LowlyDBA Nov 14 '17 at 14:07
  • If i have a survey with 500 questions and 20k users answers al of them together i will end up with 500*20k records in the Chunks table, the stored procedure for viewing the result should group them based on result ids and select question ids with them and then do a pivot operation, and by paging i mean taking n number of results at a time (sorry for my bad DBA vocabulary). And could you please explain why it is not encouraged to create and modifying the tables like that – insomniac Nov 14 '17 at 15:51
  • Also it will be much appreciated if you could provide any scenario that can bypass this rule – insomniac Nov 14 '17 at 15:56
1

Hope you have store information about House 1 and member detail somewhere.

Moreover purpose of Result Table is not clear to me.

Normally when you have slow running query then we take following steps :

i) Denormalize database : So that it reduce table join.At the same time don't disturb normalize table.So I will put SubmitDate in Chunks table

ii) Optimize query :It depend upon your query.what need to be optimize ?

iii)Introducing proper indexing

iv) Paging : As you said.

v) Partition Table

These are few step from the the best practices list and follow database design pattern.

Disadvantage of dynamic table.

i) I am not in favor of creating dynamic table like "Survey_311" because it sound like any design pattern.

ii) There will be several other kind of reports where you will at disadvantage like consolidating them will be problem.

iii) I see lot of dynamic Sql because of dynamic table.

iv) Database will be heavier very soonAlso there will be lot of indexes.

v) Maintainability : From both DBA and developer point of view.

  • Thank you for the answer, I value all the points raised against this approach. Please consider these points as well 1. The dynamic tables may not be created in the same database, It could be in another DB or even in a faster NoSQL DB. 2. There will not be any stored procedures involved in the case of the dynamic table because the sorting, paging and filtering commands will be generated from the application. 3. The application will only decide to move to the second approach when the submissions reach a certain threshold. – insomniac Nov 15 '17 at 5:52
  • I forgot to mention about NOSSQL or BIG data.But whatever I could make out from your requirement,I will go with my points. – KumarHarsh Nov 15 '17 at 5:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.