为用户/点系统设计数据库？ （在Django） [英] Designing a database for a user/points system? (in Django)

问题描述

首先，对于StackOverflow这不是一个合适的问题，对不起。我试图使它尽可能广泛。

我想创建一个有用户的数据库（MySQL，运行Django的站点），谁可以为各种类型的操作分配一定数量的积分 - 这是一个协作游戏。我的要求是获得：

• 用户的点数


• 用户的排名比较所有其他用户


• 和整体排行榜（即所有用户按点数排列）

这是我到目前为止，在我的Django models.py文件中：

  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 ）
 points_added = PointsAdded.objects.filter（user = self）
 points_total = 0 
 point_added中的点：$ b​​ $ b 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 = Tr的ue）
 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）
  

然而，对我来说，它实际上非常复杂（至少在Django查询术语中），以确定用户的排名并返回用户的排行榜，这一点很快变得很清楚。至少我觉得很难。有没有更优雅的方式来做这样的事情？

这个问题似乎表明我甚至不应该有一个单独的分表 - 人们认为什么？有独立的表格感觉更强大，但是我没有太多的数据库设计经验。

解决方案

难以将点保存在同一张表中，但这是完全值得的。如果您在用户模型上计算了总分数，则可以进行非常简单的排序/过滤操作。而且，只有当事情发生变化（不是每次想要显示）时，才可以统计总计。只需将一些验证逻辑放在post_save信号中，并确保用测试来覆盖这个逻辑，你是好的。

p.s。维基上的反规范化。

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 types 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.

解决方案

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.

这篇关于为用户/点系统设计数据库？ （在Django）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！