XP Leveling System - PHP

Rank Table

ID, Primary Key
RANK, The rank or level, 1 being the highest and 3 the lowest
MIN_SCORE, The minimum amount of point or XP needed to reach the rank

NAME, The associated name of the rank

Rank Table
+----+------+-----------+-------------------------+
| ID | RANK | MIN_SCORE |          NAME           |
+----+------+-----------+-------------------------+
|  1 |    1 |     18932 | Editor-in-Chief         |
|  2 |    2 |     15146 | Senior Technical Writer |
|  3 |    3 |     12116 | Senior Copywriter       |
+----+------+-----------+-------------------------+

Ranking Table

ID, Primary Key
FK_MEMEBER_ID, Foreign Key to member's Primary Key
FK_RANK, Foreign Key to Author Rank Table's Rank column (top)

SCORE, The member's current earned score or XP

Ranking Table
+-----+--------------+---------+-------+
| ID  | FK_MEMBER_ID | FK_RANK | SCORE |
+-----+--------------+---------+-------+
|   1 |            1 |       1 | 17722 |
|   2 |            2 |       2 | 16257 |
|   3 |            3 |       3 | 12234 |
+-----+--------------+---------+-------+

In my class I have stored the ranks -- matching those in the Rank Table -- and correlating minimum scores; RANK as key and MINIMUM_SCORE as value.

When a member's score (XP) is updated (up/down) I want to test that updated score against the below array to determine if their rank needs updating too.

private $scores = array('3' => '12116', '2' => '15146', '1' => '18932',);

Using the updated score, how could I determine the correlating rank from the above array?

Everything is open to scrutiny, this is my first time creating a ranking system so I hope to get it right :)

Solution

Ok, so given these tables:

Ranks
+------+-----------+-------------------------+
| RANK | MIN_SCORE |          NAME           |
+------+-----------+-------------------------+
|  1   |     18932 | Editor-in-Chief         |
|  2   |     15146 | Senior Technical Writer |
|  3   |     12116 | Senior Copywriter       |
+------+-----------+-------------------------+

Members
+-----------+-------+
| MEMBER_ID | SCORE |
+-----------+-------+
|     1     | 17722 |
|     2     | 16257 |
|     3     | 12234 |
+-----+-------------+

You can get a member plus their rank using:

SELECT m.MEMBER_ID, r.NAME
FROM Members m INNER JOIN Ranks r
ON m.SCORE > r.MIN_SCORE -- Pick ranks that a user is eligible for
WHERE MEMBER_ID = ?
HAVING MAX(r.MIN_SCORE) -- Pick the rank with the highest score

This allows you remove an entire table, which should make your code easier to maintain. Hopefully your rank table will be so small than it will always be in memory, but an index over both MIN_SCORE and RANK will probably be helpful.

If you determine that this join is a performance problem (please don't do this unless you've measured a performance problem), you can use a similar query to update ranks in the members table, assuming it has a RANK column:

UPDATE Members
SET RANK = (
    SELECT RANK
    FROM Ranks
    WHERE SCORE > MIN_SCORE
    AND MEMBER_ID = MEMBER_ID
    HAVING MAX(MIN_SCORE)
)