XP Leveling System - PHP

Question

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 :)

The best solution to this is probably to not have your "ranking table" and just directly join the members table and rank table, using max(rank) where min_score > score, but I can't think of how to write the query at the moment. — Brendan Long
1. Why isn't primary key in "Rank table" field "RANK" ? You could delete then primary key ID... 2. Why do you have field FK_RANK in "Ranking table"? Score is the esence here. So no update will be needed if rang changed, just save score... 3. Why isn't SCORE field in MEMBER table ? — Glavić
@BrendanLong will this be accurate won't this just give the closest rank? — Michael Rich
@MichaelRich I just posted an answer demonstrating it. I initially got the comparison backwards (should be score > min_score). — Brendan Long
@glavić 1. The answer lies in when it comes time to delete and add new ranks. 2. I thought it'd be easier application to not do a join to retrieve the rank. 3. It is for illustration purposes, in my application it's different. — Michael Rich

Brendan Long Brendan Long · Accepted Answer · 2012-09-18T21:10:57

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)
)

XP Leveling System - PHP

1 Answers