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?