Help Improving My Database Design

database-designperformance

I'm new to databases and trying to design a model in Django in which I store Contest info and the Players who play the contest. My first approach was below in which I created a Contest table that had a 1-to-many relationship with Entries.

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Entries(models.Model):
    name = models.CharField(max_length=200)
    profit = models.FloatField()
    contest = models.ForeignKey('Contest',on_delete=models.CASCADE)

However when I think about my future usage I'm going to want to, for example, query all players of a specific sport and get all of their profits. Or I may want to get the names of the top performers from the past month. I feel the way the current DB is set up won't be really efficient and as my DB grows those queries will get more intensive if I have to filter all the contests by a certain date, and then check if that player was even in the contest…etc

Essentially adding the sport and date field in Entries will make things much easier for me but from what I understand you want to avoid duplicate fields so I'm trying to approach this in the "right" way. Thanks.

UPDATED MODEL APPROACH

class Contest(models.Model):
    start_date = models.DateTimeField()
    sport = models.CharField(max_length=200)
    contest_name = models.CharField(max_length=200)
    buy_in = models.IntegerField()

class Player(models.Model):
    name = models.CharField(max_length=200)
    contest = models.ManyToManyField(Contest)

class Entries(models.Model):
    num_entries = models.IntegerField()
    profit = models.FloatField()
    player = models.ForeignKey('Player',on_delete=models.CASCADE)

So what I decided to was create a many-to-many relationship between the Contest and Player tables. And a 1-to-many from the Player to the Entries. I'm wondering is this better?

Best Answer

I'd definitely create at least 3 maybe 4 tables/models here.

  • One table contains all contents with the their details.
  • Second table with all the users/players.
  • Third table where you store the n:m relation between user and contest.
  • (Optional) Fourth table for the different types of sport.

But to be honest I'm not sure how to do this in django. Especially when it comes to the third table.