djangoormself-referencing-table

Self-referential relationship or Different Model (Django referral system)


The first example implements Self-referential relationship:

class User(models.Model):
    username = models.CharField(max_length=50, null=True)
    referrer = models.ForeignKey("self", related_name='referrals', null=True, blank=True) 
    referrer_bonus = models.IntegerField(default=0) 
    referral_bonus = models.IntegerField(default=0)

The second option uses an intermediate model:

class Referral(models.Model):
    referrer = models.ForeignKey(User, on_delete=models.PROTECT, related_name="referrals")
    referral = models.OneToOneField(User, on_delete=models.PROTECT, related_name='referral')
    referrer_bonus = models.IntegerField(default=0) 
    referral_bonus = models.IntegerField(default=0)

The question is that one referral can have one referrer. But a referrer can have many referrals.

What approach will be better if I need to use anotation to calculate the total bonus and the number of referrals. And which option will be optimal for performance?


Solution

  • There is no reason to use an intermediate model: if each User has (at most) one referral, you only make things more complicated if you add an extra model: it would require extra queries to get the (id of the) referral, and furthermore, to calculate the bonus would require enumerating over the items.

    You can refer to the self like you already did in the first example:

    class User(models.Model):
        username = models.CharField(max_length=50, null=True)
        referrer = models.ForeignKey(
            'self', related_name='referrals', null=True, blank=True
        )
        # no referrer_bonus/referral_bonus

    You however don't need to store the referrer_bonus or referral_bonus explicitly. You can just can calculate it when necessary by annotating, like:

    from django.db.models import Count
    
    User.objects.annotate(referral_bonus=Count('referrals') * bonus_per_referral).get(
        id=my_id
    )

    This prevents that the referral_bonus eventually gets out of sync.