CODEWITHSUNDEEP
mysqlasp.netdatabasedatabase-design
F U
F U

Reputation: 91

Tables with less rows vs ONE table with MANY Rows

I am creating a test site for many user to take many quizes. I want to store these results into a table. Each user can take up 5000 quizzes. My question is...Would it be better to make a table for each user and store his results into his own table (QuizID, Score)...OR...Would it be better to store ALL the results into ONE table (UserID, QuizID, Score)?

Example 5000 questions PER table * 1000 User Tables VS 1 Table with 5,000,000 rows for the same 1000 Users.

Also, is there a limit to ROWs or TABLEs a DB can hold?

Upvotes: 1

Views: 820

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

There is a limit to how much data a table can store. On modern operating systems, this is measured in Terabytes (see the documentation).

There are numerous reasons why you do not want to have multiple tables:

  • SQL databases are optimized for large tables, not for large numbers of tables. In fact, having large numbers of tables can introduce inefficiencies, because of partially filled data pages.
  • 5,000,000 rows is not very big. If it is, partitioning can be used to improve efficiency.
  • Certain types of queries are a nightmare, when you are dealing with hundreds or thousands of tables. A simple question such as "What is the average of number of quizzes per user?" becomes a large effort.
  • Adding a new user requires adding new tables, rather than just inserting rows in existing tables.
  • Maintaining the database -- such as adding a column or an index -- becomes an ordeal, rather than a simple statement.
  • You lose the ability to refer to each user/quiz combination for foreign key purposes. You may not be thinking about it now, but perhaps a user starts taking the same quiz multiple times.

There are certain specialized circumstances where dividing the data among multiple tables might be a reasonable alternative. One example are security requirements, where you just are not allowed to mix different user's data. Another example would be different replication requirements on different subsets of the data. Even in these cases, it is unlikely that you would have thousands of different tables with the same structure.

Upvotes: 5

Panther
Panther

Reputation: 3339

Ideally you should have this approach.

  1. Question Table with all the questions and primary key question Id.
  2. User table with user details.
  3. Table with 1 to many relationship having User id , quiz id and answer.

You are worrying about many rows in table but think there will be some user who will take only max 10-15 quiz. You will end up creating for 10 rows.

Upvotes: 0

Related Questions