sfmagic.org Rewrite: Standings Page Progress

08:32 Fri 28 Dec 2007
[, , ]

The standings page is almost done. I have most of the functionality I want, running into problems only with the “last ten tournaments” standings.

Selecting the standings for any year, block, sets, format, etc. is all working, and was easy to get working.

Getting the standings for “each player’s last ten tournaments” turned out to be fairly easy this time, unlike the last time around, because this time my approach is based on the player—in every case I end up with a list of all the player’s tournaments which are then summarized, so ordering those by date and grabbing the last ten is quite easy.

I thought that getting the last ten tournaments would be fairly simple too, just get the dates for those, then before summarizing for each player remove the results that don’t have a date in that set of last ten. It’s not working for some reason, and I haven’t pinned down why yet.

However, I’m sure that won’t be too difficult to work out, and so the standings page has almost all the functionality of the original, with better performance and much cleaner code.

One piece of functionality that isn’t present is keeping track of how each player’s ranking in the standings has changed since the previous week. Working that out esssentially requires doing then entire operation twice, once for the standings at the time of the requested week (which is usually the current week, but not always), and once for the standings one week before then. That would double the time required to display the page, and it really doesn’t seem worth it to do that when the plan is to create standings tables anyway, which could be easily queried for a player’s rank for any given week.

At least, I hope such a query would be easy. I’m slightly concerned at the possible size of the tables that would track standings. Each week’s standings in a single category would be about 200 rows, and with 52+ events per year that’s over 10,000 rows per year. I think that I would split the tables into categories, so that overall standings, standings for each block (or each group of sets), standings for each year, standings for seeding, and standings for “last ten tournaments” would each get their own database table. Block, sets, and year would be self-limiting and wouldn’t grow beyond about 10,000 rows, but the others would keep on growing at about 10,000 rows per year. That doesn’t seem like it should pose problems if it’s indexed by date (and player id, probably) and I’m querying it primarily by date, and it’d be a while before that grew to represent a data set similar to the 537,891 (2787 x 193) that I was trawling through yesterday.

Having the standings in their own tables will allow for better performance tracking things like rank change, and will also allow some other nice queries I’d like to have the results for, like on each player page showing that player’s highest-ever rank, their longest streak at their highest rank, etc.

Once I have the table structure for that worked out, the next part will be the population of those tables—ideally I would be able to run a script that would populate standings in each table for each category for each week in the system and only run that once, thereafter having another script that would run each week adding the new week’s standings for each appropriate category.

First, though, I need to fix the “last ten tournaments” standings.

Leave a Reply