0

I have a member website where each user can place several bets on football games every day. The idea is to sum the points of each day, calculate them and display them in a graph (in using Google chart API). All good so far. Every day new points will be calculated for each user and displayed on the graph along with the points from the past days. So that the user could see his past results in a curve or graph. My only guess is to make a Table with ID - Username - Points - Round. I don't want my database to suffer due to my poorly constructed tables and query's.

So my question is: How should I set this up in my database?

This is my table right now:

TABLE -> MEMBERS
ID
Firstname
Lastname
Username
Password

(My fictional table).

TABLE -> MEMBERS_POINTS
ID
Username
Points
Round

So I set up a query that fetches the users points from a required round. Is this good way to go? Will this slow down my database? Every user will play 30 rounds and with several users the table will probably contain thousands of row.

Find a post here but i didn't have any answers and i have some different ideas.

EDIT

It´s a member-site where every user that register have to place all of the 60 bets at once, the 60 bets are divided out over 30 days (which I call rounds). Each round consists of 2 bets, maximum 6 points. The total score will be displayed with the leader in total, along with the winner of every day. Each user should be able to see his or her past scores on in form of a graph.

1 user = 60 bets = 30 days(rounds. Total points and every day point kept in a record.

Community
  • 1
  • 1
Handsken
  • 664
  • 11
  • 26
  • 1
    I would definetly had a datetime field representing when the points and round occured in your members_point table so you can query by day, by week, by month, and so on... – legrandviking Feb 22 '13 at 21:27
  • 2
    How are rounds related to days? Can user have multiple "games" of 30 rounds? You really need to explain what the individual discrete object you want to represent and how they relate to each other. I image you also need tables for bets/action in each round as well as well? – Mike Brant Feb 22 '13 at 21:28
  • 1
    Try to explain in real-world terms (i.e. something like "The application will have any number of members, each member can have many games, each game can have many rounds, each round can have X number of bets/actions, each round will take 1 day. I would like to aggregate points for each user on a per game, per round, and/or daily basis") Your basic starting point should be any individual item your have (user, game, round) that will need to relate to the other items in a one-to-many or many-to-many fashion should have its own table. – Mike Brant Feb 22 '13 at 21:33

1 Answers1

2

There are a couple of things going on here.

Firstly, you seem concerned about performance. The design you mention should be fine from a performance point of view (though see below) - databases are very good at handling large amounts of data. On MySQL, a design like this could grow to millions of records without any real performance trouble.

Secondly, though, there is a design issue. You really should read up on database design - it's too big too answer in a single question - but your member_points table should link to the member.id column (assuming that's the primary key), not username - if the user changes their username, you have to update all their member_points records.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52