Here's one for you...

[jmcall10]

Hi people,

 

Here is one to think about, as my inferior brain cant get the maths or the query right....

 

Basically I am building a database for a five aside football match that my friends and I play each week.

 

So each week five people play five other people. However the teams are never the same and the ten people are never the same.

 

I am trying to come up with a way to log each week the results of the games and display a ladder of all the people.

 

My problem is coming up with a fair solution. You cant go on win percentage alone because if one person only ever plays one game and wins then they would have a 100% win rate. Clearly this would be wrong.

 

Any suggestions of the tables and sql involved would be much appreciated.

 

Thanks in advance

 

jmcall10

[Mchl]

Assign points. Every member of winnig team gets 3 points. Every in loosing team gets 1 point. A tie is 2 points worth.

 

 

[jmcall10]

Ah thats not so simple

 

For example, if one player plays 10 games and wins 9 and another plays 8 and wins 8.

 

The person who has played 8 and won 8 wont have as many points as the other.

 

Even tho they have won 100% of their games. Does that make sense?

 

Im trying to make the league ladder show a fair representation of how people are doing.

[Mchl]

For example, if one player plays 10 games and wins 9 and another plays 8 and wins 8.

 

But who is better player in this scenario?

[jmcall10]

that is the question I guess.

 

What is a fair way to rank these players? and how would you go about it?

 

im stuck in a moral dilema

[Mchl]

So we have at least two criteria.

Total points gained and win/loss ratio (or maybe wins/total games played)

 

For starters I'd say, introduce a threshold under which players are not ranked (i.e. you have to play in at least three matches to be ranked).

This will get rid of those one-time-winners.

 

Now let's create two separate rankings. One that will rank players by their wins/games played ratio, and the other one, that will rank players by points gained

 

    Wins%        Points         Ranking points
1     a                b                     10
2     b                c                      9 
3     d                a                      8
4   ...                 ...                    ...
5
6
7
8
9
10
...

 

Now, the higher a player is in these rankings, the more 'ranking points' he/she gets. (see 'Ranking points' column above)

 

For the final ranking, a sum of Ranking points gained in both rankings is taken into account.  The higher, the better place

 

 

 

That's general idea. I think it might need some tweaking and balancing, but I think it should work.

[jmcall10]

ok i think I follow you

 

my problem is I am useless at mysql queries

 

My "score" table looks something like this:

 

id 	playerName 	tournamentid 	points 	dateadded
1 	Andy 		130908130403 	3 	13 September 2008
2 	Barry 		130908130403 	3 	13 September 2008
3 	Robert 		130908130403 	3 	13 September 2008
4 	David 		130908130403 	3 	13 September 2008
5 	Eddie		130908130403 	3 	13 September 2008
6 	George 		130908130403 	0 	13 September 2008
7 	Jonny 		130908130403 	0 	13 September 2008
8 	Mark 		130908130403 	0 	13 September 2008
9 	Paul 		130908130403 	0 	13 September 2008
10 	Steve 		130908130403 	0 	13 September 2008
11 	Andy 		140908120610 	3 	14 September 2008
12 	Barry 		140908120610 	3 	14 September 2008
13 	Robert 		140908120610 	3 	14 September 2008
14 	David 		140908145343 	3 	14 September 2008
15 	Eddie 		140908145343 	3 	14 September 2008
16 	George 		140908145343 	0 	14 September 2008
17 	Jonny 		140908145343 	0 	14 September 2008
18 	Mark		140908145343 	0 	14 September 2008
19 	Chris 		140908145343 	0 	14 September 2008
20 	Joe 		140908145343 	0 	14 September 2008

 

So how do I code/query that?

[Mchl]

You at least need one more column for 'games played'

 

 

That's how I would plan database for such an  app

 

table players (holds data about players)
playerID, firstName, lastName, gamesPlayed, gamesWon, points
1,Andy,Foo,2,2,6
2,Barry,Bar,2,2,6
3,Robert,Baz,2,2,6
4,George,Was,2,0,2
...


table matches (holds data about matches)
matchID, datePlayed, teamAscore, teamBscore
1,13-09-2008,3,1
2,14-09-2008,1,2
...

table matches_players (assigns players to matches and teams, you can also keep some stats here like number of goals a player has scored in a match)
playerID, matchID, team
1,1,A
1,2,B
2,1,A
2,2,B
3,1,A
3,2,B
4,1,B
4,2,A
...

[jmcall10]

I see where you are going with this.

 

I reckon I will have to see my old lecturer on how this database should be built.

 

Thanks for all your help