Make an SQL table for every website user (thousands) with thousands of rows?

Question

We are creating an eLearning MultipleChoice tool where thousands of users will complete our tests. We already have thousands of subscribers to our other seminars, etc., Therefore, it is very likely that thousands will also conduct MC tests. Now we need to track each question that each user answered, how much time he took if he was right (after how many attempts), and if not, what kind of incorrect answer he gave, etc. Really a lot of data.

Now we will have thousands of questions and thousands of users. Since each question will have at least 4 answers, and we also want to track incorrect answers, my question is: in this particular case, would it be advisable to have a table for the user?

I know that the question is “for each user” ( here ), but I feel that this is really a different case.

So: one table with millions of rows or thousands of tables with thousands of rows?

Answer 1

It does not make sense to have a database object for each user. If you plan your table and database structure correctly, a million-row table is easily manageable.

The table for the user will be very unmanageable, and this is not the way relational databases are usually created.

Create one table for users and adhere to the best RDBMS practices. Implement query tuning and ensure the availability of relevant indexes in the table, as well as updated statistics.

Answer 2

The usual answer is that a "table per user" is a terrible design, and a simple solution is a separate table with additional field (s) to identify the owner.

eg. having

table_1 table_2 table_3 ...... table_999999999 id id id id ... ... ... ... 

represents a huge waste of resources, whereas

 table id user ... 

much easier to imagine.

Answer 3

Many RDMS allow you to create partitioned tables; I think this would be the best choice in your case (one partitioned table for all users, depending on the RDMS you use, you will have different options for specifying the partition key)

Answer 4

I would not use one table for each user, which would just become a maintenance nightmare.

If I tried to construct this database, I would probably try to break up the data as much as possible, so for example, you have:

User table: contains all your users Questions: contains all questions

Results: Contains all results UserID, test_id, time

Test testID, questionid, answers, time_to_complete

Answers answer_id, answer

So, in this scenario, the result table is a review, it has the usersID, the test they took, and the time it took to complete it, is an overview of everything. Then you have a test table, this is the whole test on which the user put the question to the question, so you have the question identifier, a link to the table with each answer sent by the user, and then time to complete it. The response table has every response that the user sent.

So the data will look like this:

 Results UserID, TestID, Time 1 1 00:11 Test TestID QuestionID, answers, time_to_complete 1 1 1 00:10:00 1 2 2 00:01:00 Answers answer_id, Answers 1 A 1 B 2 A 3 A 3 B 3 C 

This approach will allow you to select specific data for each user, see how many times they have passed the test, etc., and it would be much easier to manage than thousands of tables.