murphyz
11-12-2005, 04:02 PM
Hello,
I'm about to try and create a league table with php/MySQL to track games and statistics for an online poker group. Obviously, the most important thing to think about is the structure of the database, but I can't seem to find the best way to do this and was hoping someone could help provide a few ideas on how to keep it clean.
At the end of the day I should show a leaderboard, but also be able to display the results of past games - and this is what I am having trouble with when thinking about the structure.
I was thinking of a 'games' table to show:
Tournament ID.
Number of players.
Date of game.
Start time.
End time.
This information would then be put into the 'games' table and another form would be produced using the 'number of players' figure to specify the number of input boxes, thus, if there were 7 players we would have 7 boxes.
Entering the player names into this would result in a 'players' table being updated with the following:
Games Played +1
Won +1 if entered 1st place
InTheMoney +1 if 2nd or 3rd
Lost +1 if not 1st-3rd
fee +10
pay +X$ if 1st, 2nd or 3rd.
tourney Insert Tournament ID
One problem I have is that the number of places paid is usually 1-3, but would increase if the number of players entered increases to, say 18 players, and again at 27 players - this indicates I would need another table just to specify the pay structure depending on the number of entrants.
Also, the 'pay' amount differs depending on how many enter.
If 10 people enter, the pay amount would be based on 10*$10 ($100)
1st - $50 (50% of the payout)
2nd - $30 (30%)
3rd - $20 (20%).
this means if just 7 people enter, the total amount paid out would be $70.
1st - $35
2nd - $21
3rd - $14
again based on 50%, 30%, 20%
Then I would have a leaderboard page and a Games page.
My leaderboard page would need to pull out the following:
player name.
games played.
won.
ITM.
Lost (games played - won - ITM).
ITM% (Won+ITM/games played)
Buyin amount - fee
payout = pay
profit - pay-fee
ROI (return on investment) - profit/fee*100
This table should be sortable, but the initial screen should be by ROI Descending.
Games page should show a list of those who entered each game, or search by tourney id and where they finished.
At the moment there just seems to be too much information needing to track in order to keep it clean...so any thought on this would be greatly appreciated.
Cheers
Mxx
I'm about to try and create a league table with php/MySQL to track games and statistics for an online poker group. Obviously, the most important thing to think about is the structure of the database, but I can't seem to find the best way to do this and was hoping someone could help provide a few ideas on how to keep it clean.
At the end of the day I should show a leaderboard, but also be able to display the results of past games - and this is what I am having trouble with when thinking about the structure.
I was thinking of a 'games' table to show:
Tournament ID.
Number of players.
Date of game.
Start time.
End time.
This information would then be put into the 'games' table and another form would be produced using the 'number of players' figure to specify the number of input boxes, thus, if there were 7 players we would have 7 boxes.
Entering the player names into this would result in a 'players' table being updated with the following:
Games Played +1
Won +1 if entered 1st place
InTheMoney +1 if 2nd or 3rd
Lost +1 if not 1st-3rd
fee +10
pay +X$ if 1st, 2nd or 3rd.
tourney Insert Tournament ID
One problem I have is that the number of places paid is usually 1-3, but would increase if the number of players entered increases to, say 18 players, and again at 27 players - this indicates I would need another table just to specify the pay structure depending on the number of entrants.
Also, the 'pay' amount differs depending on how many enter.
If 10 people enter, the pay amount would be based on 10*$10 ($100)
1st - $50 (50% of the payout)
2nd - $30 (30%)
3rd - $20 (20%).
this means if just 7 people enter, the total amount paid out would be $70.
1st - $35
2nd - $21
3rd - $14
again based on 50%, 30%, 20%
Then I would have a leaderboard page and a Games page.
My leaderboard page would need to pull out the following:
player name.
games played.
won.
ITM.
Lost (games played - won - ITM).
ITM% (Won+ITM/games played)
Buyin amount - fee
payout = pay
profit - pay-fee
ROI (return on investment) - profit/fee*100
This table should be sortable, but the initial screen should be by ROI Descending.
Games page should show a list of those who entered each game, or search by tourney id and where they finished.
At the moment there just seems to be too much information needing to track in order to keep it clean...so any thought on this would be greatly appreciated.
Cheers
Mxx