Ranking System with Different Categories

Question

I want to create a system where the user can be ranked by several different factors.

For example, the user competes in the following categories

• Wins
• Points

I need to know what the position of the ranking in each category last week and today.

What is the best way to do this:

1. Creating a table for each category (userWins and userPoints), and update as needed?
2. Create a table with columns where I can tell that this line is part of a category?

On each approach, how do I update and sort the rank?

Imagine this scenario even though I may have 20 different categories.

Answer 1

Track the facts that drive your ranking system in normalized tables according to what makes sense for those facts without regard to how it drives your ranking system.

Then have a separate table or tables that converts your basic facts into a points system that allows you to do simple sorts for ranking.

The reason to keep these things separate is that your basic facts are driven by some kind of usage of your system, whatever that may be. You want the code for tracking this usage to be clean and maintainable. Even if you didn't have a ranking system, these facts would exist. The logic to maintain these facts is independent of the logic to calculate rankings. The ranking rules are an extra layer on top of the facts. Also, your rules for ranking could (easily) change over time, so you don't want to tangle up your basic facts with less stable rules.