Maintaining a metadata table in SQL

Question

Can someone help giving me some direction to tackle a scenario like this. A User table which contains all the user information, UserID is the primary key on User Table. I have another table called for example Comments, which holds all the comments created by any user. Comments table contains UserID as the foreign key. Now i have to rank the Users based on number of comments they added. The more comments a user added, the ranking goes up. I am trying to see what will be the best way to do this.

I would prefer to have another table, which basically contains all the attributes or statistics of a user(might have more attributes in future, right now only rank, based on comment count),rather than adding another column in User table itself.

If I create another table Called UserStats, and have UserID as the foreign Key, and have another column, called Rank, there is a possibility that everytime a user adds a comment, we might need to update the ranks. How do I write a SP that does this, Im not even sure, if this is the right way to do this.

Answer 1

You could do this from a query

SELECT UserID,
   COUNT(UserID) CntOfUserID
FROM UserComments
GROUP BY UserID
ORDER BY COUNT(UserID) DESC

You could also do this using a ROW_NUMBER

DECLARE @Comments TABLE(
     UserID INT,
     Comment VARCHAR(MAX)
)

INSERT INTO @Comments SELECT 3, 'Foo'
INSERT INTO @Comments SELECT 3, 'Bar'
INSERT INTO @Comments SELECT 3, 'Tada'

INSERT INTO @Comments SELECT 2, 'T'
INSERT INTO @Comments SELECT 2, 'G'


SELECT  UserID,
     ROW_NUMBER() OVER (ORDER BY COUNT(UserID) DESC) ID
FROM    @Comments
GROUP BY UserID

Answer 2

Storing that kind of information is actually a bad idea. The count of comments per user is something that can be calculated at any given time quickly and easily. And if your columns are properly indexed (on the foreign key,) the count operation ought to happen very quickly.

The only reason you might want to persist metadata is if the load on your database is fast and furious and you simply cannot afford to run select queries with counts per request. And that load will also inform whether you simply add a column to your user table or create a whole separate table. (The latter solution being the one for the most extreme server loads.)

Answer 3

This is not the right way to do this.

You don't want to be materializing those kinds of computed values until there is a performance problem - and you have options like Indexed Views to help you well before you get to the point of doing what you suggested.

Just create a View called UserRankings and have it look like:

SELECT c.UserId, COUNT(c.CommentId) [Ranking]
FROM Comments c
GROUP BY c.UserId

Not sure how you want to do your rankings, but you can also look at the RANK() and DENSE_RANK() functions in T-SQL: Ranking Functions (Transact-SQL)

Answer 4

A few comments:

Yes, I think you should keep the "score" metadata somewhere, otherwise, you'd have to run the scoring calc each time, which could ultimately get expensive.

Second, I don't think you should calculate an actual "rank" (vs other users). Just calculate a "score" (based on the number of comments posted), then your query can determine "rank" by retrieving scores in descending order.

Third, I would probably make a trigger that updates the "score" in the metadata table, based on each insert into the comments table.