'Game': Fun with databases

Written by Adrian Holovaty on June 11, 2004

Over the past week or so, I've had the opportunity to help put together Game, which is both a daily Web site and weekly print publication devoted to summer little-league baseball and softball in Lawrence, Kan.

The Web site is pretty cool in that it's almost completely databased -- and shows the advantages of taking extra time to define and isolate relationships between data. For instance, in the system, a game is associated with two teams. Each team is associated with a coach and a league. A league has a set of rules, a player agreement and an association with cell-phone and/or e-mail rescheduling alerts. Games take place at playing fields, which optionally have associated 360° photo VR things.

Once I defined these core relationships and our two crack Web interns started entering leagues, teams and games into the system, automating the site was a piece of cake. For the list of playing fields, I told the system, "Show me all the playing fields, ordered alphabetically, and pull out the next few games for each one." Same for the list of leagues. A league detail page displays all the teams in that league and the league's full schedule. A team detail page displays the team's full schedule (but not its roster, for child-privacy reasons). A field detail page displays that field's schedule and information about the field. A game detail page displays information about that game -- the league, the teams, the place, the result, the date/time. All of this stuff is automated based on the core pieces of information entered by our Web producers and key local-sports-guru-Web-editor Levi Chronister.

My favorite part of the site is this: Because little-league games are so dependent on weather conditions, and because we conveniently have weathermen on staff who populate a database of forecasts for our weather site, Simon had the brilliant idea of cross-linking game detail pages with weather forecasts. So, if a game hasn't happened yet, the system checks our weather database for a forecast for that day and displays it. The beautiful thing about that is it's absolutely "free," because we were already compiling and storing that information, anyway. All it took was about 15 minutes of extra programming.

Comments

Posted by Gary Love on June 12, 2004, at 12:06 a.m.:

Adrian,

A question, a comment, and a congratulations for ya.

Question: What database and scripting language did you use for this? It looks like there's alot of dynamic information being connected in real-time...but it still loads up nice a quick.

Comment: It's too bad that you weren't able to go down to the player level (for understandable privacy concerns). It would be exciting to give each player a unique id, then to be able to follow their progress from little league, to preps, to playing for the Jayhawks.

Congratulations: I love the little touches with this. The clean and guessable urls, the contextual information (weather is a nice touch), and the wireless integration.

Posted by Simon Willison on June 12, 2004, at 1:12 a.m.:

I can answer your technology question: Our database is PostgreSQL and our scripting platform is mod_python running on Apache. We're very much an open-source shop.

I'm glad you mentioned the URLs; we're both sticklers for smart URL schemes so it's great to have someone finally notice them!

Posted by Adrian on June 12, 2004, at 4:06 a.m.:

Thanks, Gary! As Simon suggested, we're big Python fans. Evidently some companies out there don't disclose that they use Python, as some sort of competitive advantage, but I'll shout it from the rooftops: We use Python, and it rocks!

Posted by Bryan on June 13, 2004, at 8:18 a.m.:

Did you use any third party Python libraries or frameworks to do this? Or was it "vanilla" mod_python programming? If the former, I'd be really interested in hearing about it.

Posted by Levi on June 13, 2004, at 8:28 a.m.:

No mention of me? ;-)

Posted by Adrian on June 13, 2004, at 8:39 p.m.:

Bryan: Nope, we haven't used any third-party Python frameworks...We looked into a few of them a while ago, but nothing seemed to fit our needs 100%, so we wrote our own framework. It's not quite "vanilla" -- it's quite abstracted from mod_python (i.e., we have our own concepts of request and response objects, and mod_python just happens to be the front-end for it).

Simon writes about our dealings with Python code every once in a while, if you're interested. On this site, I try to keep the focus more on online journalism than on code.

Posted by Adrian on June 13, 2004, at 8:43 p.m.:

Levi: Fixed. :)

Posted by Levi on June 14, 2004, at 3:44 p.m.:

Thanks. I was harassing you more than anything else, but I appreciate the nod ;-)

Posted by Bob on June 16, 2004, at 10:49 p.m.:

Can you provide some details on how the rescheduling notifications work? I maintain a website for a high school soccer team. We use the web and email notifications, but adding notifications via SMS would be very handy.

You guys do some very cool work!

Bob

Posted by Adrian on June 16, 2004, at 10:56 p.m.:

Bob: I wrote a little about how it works in August 2003. Hope that helps.

Posted by Bob on June 17, 2004, at 12:21 a.m.:

Thanks! I expected it to be much more complicated.

Bob

Comments have been turned off for this page.