Designing a database for a user/points system? (in Django)

First of all, sorry if this isn't an appropriate question for StackOverflow. I've tried to make it as generalisable as possible.

I want to create a database (MySQL, site running Django) that has users, who can be allocated a certain number of points for various 开发者_如何学Gotypes of action - it's a collaborative game. My requirements are to obtain:

• the number of points a user has
• the user's ranking compared to all other users
• and the overall leaderboard (i.e. all users ranked in order of points)

This is what I have so far, in my Django models.py file:

class SiteUser(models.Model):
    name = models.CharField(max_length=250 )
    email = models.EmailField(max_length=250 )
    date_added = models.DateTimeField(auto_now_add=True) 
    def points_total(self):
        points_added = PointsAdded.objects.filter(user=self)
        points_total = 0
        for point in points_added:
            points_total += point.points
        return points_total

class PointsAdded(models.Model):
    user = models.ForeignKey('SiteUser')
    action = models.ForeignKey('Action')
    date_added = models.DateTimeField(auto_now_add=True) 
    def points(self):
        points = Action.objects.filter(action=self.action)
        return points

class Action(models.Model):
    points = models.IntegerField()
    action = models.CharField(max_length=36)

However it's rapidly becoming clear to me that it's actually quite complex (in Django query terms at least) to figure out the user's ranking and return the leaderboard of users. At least, I'm finding it tough. Is there a more elegant way to do something like this?

This question seems to suggest that I shouldn't even have a separate points table - what do people think? It feels more robust to have separate tables, but I don't have much experience of database design.

this is old, but I'm not sure exactly why you have 2 separate tables (Points Added & Action). It's late, so maybe my mind isn't ticking, but it seems like you just separated one table into 2 for some reason. It doesn't seem like you get any benefit out of it. It's not like there's a 1 to many relationship in it right?

So first of all, I would combine those two tables. Secondly, you are probably better off storing points_total into a value in your site_user table. This is what I think Demitry is trying to allude to, but didn't say explicitly. This way instead of doing this whole additional query (pulling everything a user has done in his history of the site is expensive) + looping action (going through it is even more expensive), you can just pull it as one field. It's denormalizing the data for a greater good.

Just be sure to update the value everytime you add in something that has points. You can use django's post_save signal to do that

It's a bit more difficult to have points saved in the same table, but it's totally worth it. You can do very simple ordering/filtering operations if you have computed points total on user model. And you can count totals only when something changes (not every time you want to show them). Just put some validation logic into post_save signals and make sure to cover this logic with tests and you're good.

p.s. denormalization on wiki.