Optimized conception for score table

Question

I have a simple question yet I couldn't find a direct answer/explanation. Sorry if duplicate

I want to build a score table for a small quiz game where users get questions and they can either be right or wrong. So, I figure I have 2 choices, either a table with 1 row = 1 user answer, like this:

userId	rightAnswer	wrongAnswer
1	0	1
1	0	1
1	1	0
...	...	...

Or a table with 1 row = 1 user global score:

userId	rightAnswers	wrongAnswers
1	42	21
2	100	0
3	12	13
...	...	...

I have pretty much 0 experience with database/sql optimization, so i don't know which one is the most efficient.

Here is my thought process:

First option: Better(?)/Faster(?) to add/update a score because I don't care about the current table state, I just have to insert a row But, to get a user's global score I would need a heavy(?) query like

SELECT SUM(right), SUM(wrong) WHERE userId = x;

Since I would want to show a user's global score on each page/request, I feel like this is not the smart choice. Plus, since 1 row = 1 user answer, the table could get very very large.

First option: Slower(?) to add a score, since I would have to update an existing row. Meaning SELECT then UPDATE, on each answer. Not particularly my case, but this option is less flexible since - unlike the first option - I wont be able to store which question was rightly/wrongly answered. But, with this I can get a user's total score without a heavy query. Would be better if an UPDATE query could return the resulting row, so I don't have to UPDATE then SELECT to show.

Lastly, if you are kind enough to spend time answering me, could you explain briefly one or the other is technically better, and how could I test it (is there some sort of tool/procedure)

Thanks

Answer 1

FWIW, I know you're asking about logical design so this is more theory-based, but knowing which database system you plan to use actually is relevant to the answers, based on features available within that database to facilitate improved performance. Typically any questions around performance have to be backed by specifics.

First Option:

Better(?)/Faster(?) to add/update a score because i dont care about the current table state, i juste have to insert a row

This is better from a locking perspective. Inserting a new row won't lock the table from being read from on the other rows. Though depending on Transaction Isolation Level being used in the database (depending on the database system) this becomes a moot point, if it uses optimistic concurrency. Optimistic concurrency is a methodology that allows writers to not block readers and visa versa, typically implemented via maintaining the previous version of the row and making it available when it's actively being changed.

Plus, since 1 row = 1 user answer, the table could get very very large.

Large is a relative term. Data size at rest is irrelevant though. Most modern database systems handle tables with trillions of rows and petabytes of data just fine. It comes down to your use cases, how you design your queries for those use cases, and how you architect your database accordingly.

Second Option:

Slower(?) to add a score, since i would have to update an existing row. Meaning SELECT then UPDATE

You would only have to do a filtered UPDATE with a WHERE clause, so it would still be a single query. With the proper index, this should only have to seek to the specific user's score row and should be a very quick change. But you run into potential blocking issues, unless again, you're using optimistic concurrency, then that concern becomes moot.

Not particularly my case, but this option is less flexible since - unlike the first option - i wont be able to store which question was rightly/wrongly answered.

That would've been my first go-to on designing it as option one, as it would seem natural one might want to know which questions were answered correctly or not. This is generally typical in a quiz database.

But, with this i can get a user's total score without a heavy query.

With the first option you can as well. Depending on the database system you're using, a Columnstore Index, Materialized View, or even regular Rowstore Indexing might be sufficient, to accomplish your goal efficiently here.

Would be better if an UPDATE query could return the resulting row, so i dont have to UPDATE then SELECT to show.

Most database systems can do this with the OUTPUT or RETURNING clause of a DML statement.

Lastly, if you are kind enough to spend time answering me, could you explain briefly one or the other is technically better, and how could i test it (is there some sort of tool/procedure)

This is the in-practice part of your question. No one can say as the only way to find out for your use case is to actually throw data at them and test them. From a business logical perspective, option one would be my go-to though. I wouldn't be concerned about a performance problem I don't have (yet) and there's plenty of tools, as previously mentioned, to help improve performance.

Answer 2

Just storing the total isn't really comparable, as it gives you little info on what actually happened. Most likely you would be comparing to maintaining both the Answer table and the User table.

A query or view is much better, as you are not at risk of update anomalies, and you don't slow down your inserts with triggers. But this comes at the large cost of querying the whole set (at least for a single user) just to get their score.

If you are using SQL Server then the right answer is an Indexed View. The server automatically maintains the data in the view synchronously (as opposed to Materialized views in other DBMSs).

This allows a GROUP BY as long as you follow particular restrictions, primarily using COUNT_BIG and SUM only, and only INNER JOINs.

It also must be schema-bound, preventing major changes to the base tables.

CREATE VIEW dbo.TotalUserScores
WITH SCHEMABINDING
AS

SELECT
  a.UserId,
  COUNT_BIG(*) AS AnswerCount,
  SUM(a.Right) AS RightCount,
  SUM(a.Wrong) AS WrongCount
FROM dbo.Answer a
GROUP BY
  a.userId;

Then add the index, which must be unique and clustered (you can add other indexes as well):

CREATE UNIQUE CLUSTERED INDEX CX ON dbo.TotalUserScores (UserId);

Make sure when you query it that you use the WITH (NOEXPAND) hint, for various performance reasons.