CODEWITHSUNDEEP
mysqldatabase-design
Pdksock
Pdksock

Reputation: 1030

Database design for a Fantasy league

Here's the basic schema for my database

Table user{  
      userid numeber primary key,  
      count number  
}

Table player{  
      pid number primary key,  
}

Table user-player{  
      userid number primary key foreign key(user),  
      pid number primary key foreign key(player)  
}

Table temp{  
      pid number primary key,  
      points number  
}

Here's what I intend to do...

My questions is considering 200 players and 10000 users,Will this method be efficient? I am going to be using mysql for this.

Upvotes: 3

Views: 4113

Answers (1)

Joel Brown
Joel Brown

Reputation: 14388

People often seem to be worried about performance for small databases. Let the DBMS do what it is designed to do. If you find in practice - or preferably under load testing - that you have a performance problem, then take steps to deal with it. Otherwise don't pre-optimize!

Instead of using a temporary table to store one batch of player scores, store all player scores in a tranactional table.

Remove the user.count column and replace your temp table with something like this:

Table player_points{  
      pid number primary key,  
      match_date datetime primary key, 
      points number
}

With this you can easily calculate any user's score. You can even recalculate any user's score as of a given date. This is much more powerful and much simpler to maintain. Keeping a current snapshot only makes it impossible to manage should anything go wrong or should one of your users challenge their score.

This query gives you the scores for all users. You can add filters to it to do other things like finding the score for a single user or showing a leader board.

select 
  U.userid as UserID
, sum(S.points) as TotalScore
from user S 
  inner join user-player J
    on S.userid = J.userid
  inner join player_points S
    on J.pid = S.pid
group by
  U.userid

This query would give you a leader board:

select 
  U.userid as UserID
, sum(S.points) as TotalScore
from user S 
  inner join user-player J
    on S.userid = J.userid
  inner join player_points S
    on J.pid = S.pid
group by
  U.userid
order by TotalScore desc
limit 10

This query would give you points awarded to a user by date, which you could graph as-is or cumulatively, for example.

select 
  S.match_date as MatchDate
, sum(S.points) as TotalScore
from user-player J
  inner join player_points S
    on J.pid = S.pid
where J.userid = 123 -- The user ID you want.
group by
  S.match_date 
order by S.match_date

Upvotes: 7

Related Questions