Geek Answers Handbook

What is the best way to store / count user points?

I want to create a database for a website where users can score points (reputation) to perform certain actions and struggle with the design of the database.

I plan to keep track of the things that the user does so that they can have 25 points for the item they sent, 1 point for the 30 comments they made, and 10 more bonus points for being awesome!

Obviously, all the data will be there, but it looks like a lot or a request for a common rating for each user, which I would like to display next to their username (in the form of level). For example, a query on the table of submitted elements to get ratings for each element from this user, a query on the table of comments, etc. If all this needs to be done for each user mentioned on the page .... MANY requests

I thought about storing the account in the user table, which would look much faster, but I used it to store data that can be calculated from other data, BAD!

I have seen many sites that do similar things (even stack overflows do similar), so I believe that "best practice" should follow. Can anyone suggest this could be?

Any suggestions or comments would be great. Thanks!

+5
source share
4 answers

I think this is certainly a big question. I had to create systems that have a similar situation with this, especially when a table with ratings in it is available quite often (for example, in your scenario). Here is my suggestion for you:

First, create some tables like the following (I use SQL Server best practices, but call them what you think is right):

UserAccount          UserAchievement
 -Guid (PK)           -Guid (PK)
 -FirstName           -UserAccountGuid (FK)
 -LastName            -Name
 -EmailAddress        -Score

, , (, SQL, ):

SELECT [UserAccount].[FirstName]      AS FirstName,
       [UserAccount].[LastName]       AS LastName,
       SUM([UserAchievement].[Score]) AS TotalPoints
FROM [UserAccount]
INNER JOIN [UserAchievement]
     ON [UserAccount].[Guid] = [UserAchievement].[UserAccountGuid]
GROUP BY [UserAccount].[FirstName],
         [UserAccount].[LastName]
ORDER BY [UserAccount].[LastName] ASC

, , , . ; , ( , UserAccountGuid) .

- UserAccount , , , . -, 3-10 . , , "IN", .

+3

/ . , ( ). , .. . , . , , - .

, , .

+1

- cron - SUM . " ", , .

, , cron, , .

0

, , , . .

User ratings will be constantly updated and can be quickly requested for display.

0
source

More articles:


All Articles