Concepção otimizada para tabela de pontuação

FWIW, sei que você está perguntando sobre design lógico, então isso é mais baseado em teoria, mas saber qual sistema de banco de dados você planeja usar é realmente relevante para as respostas, com base nos recursos disponíveis nesse banco de dados para facilitar o desempenho aprimorado. Normalmente, qualquer dúvida sobre desempenho deve ser apoiada por detalhes.

Primeira opção:

Melhor(?)/Mais rápido(?) para adicionar/atualizar uma pontuação porque não me importo com o estado atual da tabela, apenas preciso inserir uma linha

Isso é melhor do ponto de vista do bloqueio. A inserção de uma nova linha não impedirá que a tabela seja lida nas outras linhas. Embora dependendo do nível de isolamento de transação usado no banco de dados (dependendo do sistema de banco de dados), isso se torna um ponto discutível, se usar simultaneidade otimista. A simultaneidade otimista é uma metodologia que permite que os escritores não bloqueiem os leitores e vice-versa, normalmente implementada por meio da manutenção da versão anterior da linha e da disponibilização dela quando ela estiver sendo alterada ativamente.

Além disso, como 1 linha = 1 resposta do usuário, a tabela pode ficar muito grande.

Grande é um termo relativo. O tamanho dos dados em repouso é irrelevante. A maioria dos sistemas de banco de dados modernos lida perfeitamente com tabelas com trilhões de linhas e petabytes de dados. Tudo se resume aos seus casos de uso, como você projeta suas consultas para esses casos de uso e como você arquiteta seu banco de dados de acordo.

Segunda opçao:

Mais lento (?) Para adicionar uma pontuação, pois teria que atualizar uma linha existente. Significa SELECIONAR e depois ATUALIZAR

Você só teria que fazer um filtro UPDATEcom uma WHEREcláusula, então ainda seria uma consulta única. Com o índice adequado, isso deve buscar apenas a linha de pontuação do usuário específico e deve ser uma mudança muito rápida. Mas você se depara com possíveis problemas de bloqueio, a menos que, novamente, esteja usando simultaneidade otimista, então essa preocupação se torna discutível.

Não é particularmente o meu caso, mas esta opção é menos flexível, pois - ao contrário da primeira opção - não poderei armazenar qual pergunta foi respondida correta/incorretamente.

Essa teria sido minha primeira tentativa de projetá-lo como opção um, pois pareceria natural que alguém quisesse saber quais perguntas foram respondidas corretamente ou não. Isso geralmente é típico em um banco de dados de questionário.

Mas, com isso, posso obter a pontuação total de um usuário sem uma consulta pesada.

Com a primeira opção você também pode. Dependendo do sistema de banco de dados que você está usando, um índice Columnstore, uma visualização materializada ou até mesmo uma indexação Rowstore regular pode ser suficiente para atingir seu objetivo com eficiência aqui.

Seria melhor se uma consulta UPDATE pudesse retornar a linha resultante, então não preciso UPDATE e depois SELECT para mostrar.

A maioria dos sistemas de banco de dados pode fazer isso com a cláusula OUTPUTou RETURNINGde uma instrução DML.

Por último, se você tiver a gentileza de dedicar algum tempo para me responder, poderia explicar brevemente um ou outro é tecnicamente melhor e como posso testá-lo (existe algum tipo de ferramenta/procedimento)

Esta é a parte prática da sua pergunta. Ninguém pode dizer que a única maneira de descobrir o seu caso de uso é realmente lançar dados neles e testá-los. De uma perspectiva lógica de negócios, a primeira opção seria minha escolha. Eu não ficaria preocupado com um problema de desempenho que (ainda) não tenho e há muitas ferramentas, como mencionado anteriormente, para ajudar a melhorar o desempenho.

Apenas armazenar o total não é realmente comparável, pois fornece poucas informações sobre o que realmente aconteceu. Muito provavelmente você estaria comparando a manutenção da tabela e da tabela .AnswerUser

Uma consulta ou visualização é muito melhor, pois você não corre o risco de anomalias de atualização e não retarda suas inserções com gatilhos. Mas isso acarreta um grande custo de consultar todo o conjunto (pelo menos para um único usuário) apenas para obter sua pontuação.

Se você estiver usando o SQL Server, a resposta certa é Indexed View . O servidor mantém automaticamente os dados na visualização de forma síncrona (em oposição às visualizações materializadas em outros SGBDs).

Isso permite, GROUP BYdesde que você siga restrições específicas, principalmente usando COUNT_BIGe SUMsomente, e somente INNER JOINs.

Ele também deve estar vinculado ao esquema, evitando grandes alterações nas tabelas base.

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;

Em seguida, adicione o índice, que deve ser único e clusterizado (você também pode adicionar outros índices):

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

Ao consultá-lo, certifique-se de usar a WITH (NOEXPAND)dica, por vários motivos de desempenho .