Published: Wednesday, September 27, 2000
An Ingenious Banner Rotation System
By Bill Graziano
This all started when I was trying to get banner ads up to cover the hosting costs for the site. I started
using a company called BurstMedia. I've been fairly happy with them
overall but I also wanted more flexibility in my advertising. I looked at downloading some free banner rotation
software but I didn't find anything I was excited about. Plus, none of software used the cool new way to select
a random record that I'm going to cover in this article. I had three main goals for this software:
- First, it had to be simple and easy to implement. Whenever I add a new feature to the web site I like to
make it as simple as possible. I always learn so much about what I really want while I'm using the feature I
hate to invest much time up front.
- Second, it had to support advertising networks, link exchanges and serving my own ads off the site. My
site is still relatively new and I'm still working on the best ways to generate revenue.
- Finally, I wanted a solution that I could build on as I required more features. Nothing in my first
requirement should make my work harder in the future. That sounds like a contradiction but I'll explain more
in the article.
The Database Table
I managed to accomplish this using one table, two stored procedures and two ASP scripts. You can
download a script to create all the objects and the ASP pages at
the end of the article. We'll start with the table I use to hold my ads. I'm a database guy at heart. Get the
database design right and the rest of the application will follow. My only database table looks like this:
BANNER_Ads
|
|---|
| Column Name | Datatype | Extra Info |
|---|
AdId | [int] | IDENTITY (1, 1) |
AdName | [char] (100) | NOT NULL |
AdType | [char] (10) | NOT NULL |
ImageURL | [varchar] (100) | NULL |
Height | [smallint] | NULL |
Width | [smallint] | NULL |
ALTText | [varchar] (100) | NULL |
HTML | [varchar] (2000) | NULL |
Weight | [smallint] | NULL |
WeightScaled | [smallint] | NULL |
LinkToURL | [varchar] (100) | NULL |
TrackImpressions | [char] (1) | NULL |
TrackClicks | [char] (1) | NULL |
AdId is the unique identifier for the table. It is an identity column, the primary key and a unique
clustered index. I use AdType to indicate if this is an "HTML" ad or an "Image" ad. For an HTML ad
I'm going to take the contents of the HTML field and just dump it to the screen. I use this for banner exchanges
and ad networks. An "image" ad is one where the image resides on my server. In this case I could have just put
the HTML to display the image in the HTML field and been done with it. That does limit your flexibility though.
At some point I'm going to write and ad redirector ASP page with click tracking. At that point I can just update
the LinktoURL field and all my ads will now run through my ad redirector. The ImageURL,
Height, Width and ALTText fields do just what you think they do.
TrackImpressions and TrackClicks are two Y/N flag fields that indicate whether I'm going
to track impressions and clicks on a per ad basis. I don't use these at this point and really didn't need to
put them in.
The Weight and WeightScaled field are what drive the random selection of an ad. Weight
is just an integer weight assigned to each record. It's not a percentage; rather, it is a weight relative to each other
ad. WeightScaled is filled in by the stored procedure I'll describe below. WeightScaled
has a non-unique index.
In Part 2 we'll look at the banner administration
ASP page.
Read Part 2
