sfmagic.org Rewrite: Planning Head-to-Head
Tracking head-to-head results is something I’ve been thinking about doing for quite some time. It’s an obvious feature, but I shied away from it first because it seemed complicated on the database side when I started the original site and later because the reporting interface seemed like it would have to be clunky.
Now, however, I don’t think it’s that complicated, and I think that a JavaScript-enhanced interface for entering the results of each match could be almost as fast as my current (also JavaScript-enhanced, but to a lesser extent) reporting page.
With the ability to report multiple tournaments without a page refresh, the new version might end up being faster, in total, than the current version.
For head-to-head, I was going to represent each match by two rows in a table, one for each player’s results. I gravitate towards that because it makes for the easiest queries, but my good friend Niall Murphy convinced me not to duplicate data, and to represent both in one row. So the winner will always be in one row, and the player who gets paired up will be the winner in a draw, and if the players aren’t paired again, I guess the player who places higher in the final results will be. I hope that the concept of draws doesn’t screw up this model, but I can’t see where it would offhand. I am of course tempted to flag a result as a draw, but it should be pretty easy to derive that. So I think the fields would be:
player1
player2
wins
losses
draws
tournament_id
resultid1
resultid2
The wins, losses, and draws would all be from the perspective of player 1. The inclusion of tournament_id is dubious, because it’s also available from the result rows referenced by resultid1 and resultid2, so I might remove it also. The two resultid references are ungainly, but since each player will have a row in the results table (meaning the standard results table, not the head-to-head results table), I need to have both of those.
PseudoSQL for the query “what’s my game record against my nemesis?” would be along the lines of (first pass here, so probably not as elegant/efficient as it should be, and it’s clearly not real SQL syntax):
select
sum(wins) as wins1, sum(losses) as losses1, sum(draws) as draws1
from h2hresults
where
player1 = 'Tadhg' and player2 = 'Nemesis',
sum(losses) as wins2, sum(wins) as losses2, sum(draws) as draws2
where
player1 = 'Nemesis' and player2 = 'Tadhg',
sum(wins1, wins2) as wins, sum(losses1, losses2) as losses, sum(draws1, draws2) as draws
Assuming I’m not missing something, that seems reasonable.
Draws do screw up the easiest approach to the match win-loss record, i.e. counting the rows where I’m player1 and my nemesis is player2 as wins, and rows where I’m player2 and my nemesis is player1 as losses—because of draws, some extra clauses are required to deal with it.
Overall, though, I think it should work, and shouldn’t take too long to set up. The interface for entries might be the toughest part, but I think I have a good idea about how to tackle that.
05 Jan 2008 at 01:02
I’m delighted by how you have turned the SFMagic rewrite into an exercise in strict algorithmic thinking. Reading about the process of how you make good code is inspiring, and you are admirable for applying yourself thusly. I’ve often wondered who is the real audience of SFMagic.org. Is it the handful of statistics-mad, hyper-competitive players in our group? Is it the all-seeing Deity of Equanimity and Reason, to whom it is an offering? Or is it Tadhg’s own exacting meticulousness and autodidacticism? Regardless, it is a great piece of work to be admired in its own right.