Mysql – Ranking System with Different Categories

The dynamic rank would seem more fluid.

As long as you index the rank field, updating the points gives the rank index a workout and gets the rank dynamically sorted in a logical manner at the same time.

The execution of the query would be

• query parsing
• query optimization
• rank index scan (if rank is indexed)
• limit on result set determined by value of dynamic variable

Updating points and then running a single select would execute the query like this

• query parsing
• query optimization
• points index scan (if points is indexed)
• limit on result set determined by the compiled query

Sorting ranks vs sorting points could be just a matter of preference.

However, there is something to consider. If you rank by top 10,000 or 20,000, rank should be a SMALLINT. If the points is also SMALLINT (< 65536 or 64K), then either index/query scenario is fine. However, if points can go beyond 64K and need INT, then go with indexing the rank and dynamically assign the rank.

I would also store rank and point together in the same table to avoid the need to do JOINs. It already has a full plate doing sorts via the index on the points/rank.

One way to understand what's going on with normalization is to find out what normalization buys you. In other words, what's going to happen if you depart from any given normal form?

For normalization forms second through fifth normal forms, the answer is "update anomalies". That is, when you insert, update, or delete rows, can you end up leaving things in a bad way? One particular kind of bad way is a database that contradicts itself, that stores a given assertion somewhere in the database and stores a contradictory assertion somewhere else in the database.

Normalization helps with this by obviating the problem. That is, it cuts down or eliminates the possibility of contradictory assertions by assuring that facts are stored once, and only once. The fact is either right or it's wrong, but it's not self contradictory.

There are other ways to avoid self contradictory databases, but they involve careful, disciplined programming. There are situations where a non normalized approach gives you more benefits than a normalized approach. But this calls for judgement, and judgement comes with experience.

With regard to EAV, one attraction to EAV is that it allows you to get started without coming up with a logical model for the structure of the data. The reason is that any logical model whatsoever can conform to EAV. So why doesn't everybody do EAV? Because sometimes, the data that results is relatively worthless compared to the expected value when the database was built. I'm not saying EAV is always wrong. I am saying that it often is. Again, this calls for judgement, and judgement comes with experience.