ASP Football Pool

[Updated April 24, 2007 to include the 2007 NFL schedule in the code download.]

Now for a real web application. This one implements a weekly, online football pool similar to contests often featured in newspapers or played at the workplace.

Sports pools vary in features. Some include tie breakers, use confidence points for scoring, employ point spreads, etc. For this application a simple "pick 'em" format with a single tie breaker is used.

Overview

Every week during the football season, contestants enter by guessing the winner in each of the week's games. Whoever guesses the most games correctly wins. Contestants are also asked to guess the total number of points that will be scored in the Monday Night game. In the event of a tie, the player or players who guess closest to that total win.

In addition to the entry forms, there are pages to display contest results, game schedules, standings, etc.

Usernames and passwords are used to identify individual users and control access to the application. An administrator (usually the person who runs the web site and installs the application) sets up the users and updates the game scores each week.

The Scope of This Article

To follow this example, you should have a working knowledge of ASP including the VBScript, server-side includes, session variables and how to build a Microsoft Access database and access it via ASP.

Unlike most other examples featured on the site, this one is fairly large and involves a number of scripts and data. So, only a high-level overview is given. The major aspects of the application design and logic are explained and details are given only for selected features.

To view any of the source code online, see the listing at the end of this article.

The Online Demo

To get an idea of how the application works, take a look at the demo. You can sign on as the administrator (username "Admin", password "admin") or as one of the players (use the password "touchdown" for any of these users).

The demo is limited in functionality. For example, you can't change any passwords, and it's frozen to a particular date during the sample football season. But it should help you understand the concept. A full working version is available for download (63KB) which you can install on your own web server and experiment with.

One helpful page is in fact, the help page. It covers the available user functions and explains the contest rules. Be sure to look them over as they define many of the application's requirements.

Database Design

The diagram below shows the individual tables, columns and relationships used in the application database. It's designed to hold data for a single season and so some tables need to be reinitialized each year although the basic structure should not vary (see notes for each table).

In order for the application to work, some of the tables need to be initialized with data while others should start empty. This is noted in the descriptions below. The links on the table names will open up a display of the table contents from the demo database.

Teams
Contains a record for each team in the league including home city and name, conference and division. This table is fairly static but may need to be changed from year to year as teams move or new teams join the league.

Schedule
Contains a record for every single game, organized by week number. The data includes the date and time of the game and the IDs of the home and visiting teams. These fields need to be set before the start of the season.

The columns for the game results (scores, result and overtime flag) are initially empty. They will be set whenever the administrator updates a game score via the application.

Users
Contains the IDs and passwords for all users. Initially only a single ID and password is loaded for the administrator. He or she can then login and create additional users.

Picks
Initially empty, new records are added whenever a user makes a weekly entry. The fields record the user ID, game ID and the user's pick for that game.

TieBreaker
Like the Picks table, this one starts out empty and is filled when a user submits a weekly entry. It stores the user's tie breaker score total for a given week.

Although most of the data applies to a single season, you can reuse some of the more permanent data, such as the Teams and Users tables, with whatever minor changes are necessary.

Next