Building a PHP Ad Tracker: Creating the Database Tables

For the vast majority of modern websites, advertising is a major source of revenue.

Banner ads for services related to the web site owner’s industry allow customers to view products that tie into the site owner’s core business. In exchange, the web site owner can charge the banner ad owner for every impression (banner ad appearance) or click-through (when a user clicks the banner ad).

In order to build a banner ad management system, we must first create the database tables that will hold the ad information, the client data and the activity schedule for each ad.

Step 1: Create MySQL Advertisement Table

First, we must build a table to hold the data on each particular banner ad. The table will contain:
·    a primary key ID number for each ad
·    a foreign key ID number (tied to the client ID)
·    the title of the ad
·    the URL.that the page will redirect to when the ad is clicked
·    the file path of the ad banner
·    the ad’s “active” status (active/inactive)
·    the ad’s “deleted” status
·    the date the ad was created
·    the last date the ad was modified
·    the date the ad was deleted

The MySQL script below will create the devdrive_ads table:

CREATE TABLE devdrive_ads (
ad_id int(10) NOT NULL auto_increment,
ad_client_id int(10) NOT NULL default '0',
ad_title varchar(200) NOT NULL default '',
ad_url varchar(255) NOT NULL default '',
ad_path varchar(255) NOT NULL default '',
status int(1) NOT NULL default '1',
deleted int(1) NOT NULL default '0',
deleted_dt datetime default NULL,
created_dt datetime NOT NULL default '0000-00-00 00:00:00',
modified_dt datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (ad_id)
) TYPE=MyISAM;

Step 2: Create the MySQL Clients table

Each banner ad must have a client. Since some clients may have multiple banners, we need a separate table for the client data, rather than including it in each line of the devdrive_ads table.

The clients table will contain:
·    a primary key ID number
·    the client’s business name
·    the name of the primary contact at the client’s office
·    the client’s email address
·    the client’s phone number
·    the client’s “active” status
·    the client’s “deleted” status
·    the date the client record was created
·    the last date the client record was modified
·    the date the client record was deleted.

The MySQL script below will create the devdrive_ads_clients table:

CREATE TABLE devdrive_ads_clients (
ad_client_id int(10) NOT NULL auto_increment,
ad_client_name varchar(100) NOT NULL default '',
ad_client_contact varchar(100) NOT NULL default '',
ad_client_email varchar(100) NOT NULL default '',
ad_client_phone varchar(20) NULL default NULL,
status int(1) NOT NULL default '1',
deleted int(1) NOT NULL default '0',
deleted_dt datetime default NULL,
created_dt datetime NOT NULL default '0000-00-00 00:00:00',
modified_dt datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY  (ad_client_id)
) TYPE=MyISAM;

Step 3: Create the MySQL Activity table

The activity table will monitor how each ad performs, both in terms of views and click-throughs. We will also monitor the month and year of ad activity, as some ads may perform better in some parts of the year (4th of July sales, after-Christmas sales) than others.

The MySQL script below will create the devdrive_ads_activity table:

CREATE TABLE devdrive_ads_activity (
ad_activity_id int(10) NOT NULL auto_increment,
ad_id int(10) NOT NULL default '0',
ad_view_cnt int(15) NOT NULL default '0',
ad_click_cnt int(15) NOT NULL default '0',
ad_activity_month int(2) NOT NULL default '0',
ad_activity_year int(4) NOT NULL default '0',
PRIMARY KEY  (ad_activity_id),
KEY ad_id_rel (ad_id)
) TYPE=MyISAM;

In the next lesson, we will look at the PHP class constructor that will give us the variables and methods used throughout the rest of the application.

Keep an eye out next week for part two.

SHARE THIS POST
  • john jeeves

    hmmm, how about if the stats need to record detailed location and time details…seems your devdrive_ads_activity table doesnt allow for that

  • Anonymous

    Coding is something like it can not be used without understanding.

  • http://github.com/williamn William Notowidagdo

    In the last SQL snippet, what you probably mean is ‘devdrive_ads_activity’ instead of ‘wrox_ads_activity’?

    • Anonymous

      Nice catch William, thanks for letting us know. The code reads properly now.

  • Ole

    I believe you really, really want to use InnoDB tables for the activity table.

    With MyISAM, each time you’re going to insert/update a record it will lock the whole table, which will cause serious performance issues.

    InnoDB will only lock the row it’s operating on, so that is really the way you want to go.
    – However even with InnoDB, once your site grows beyond 2-3M page views each day, you’ll start to face some more performance problems. But that’s another story for another day ;)

    • Anonymous

      Good points on both. What you said about InnoDB’s performance issues for sites with more activity stresses the importance of understanding what the requirements of your project are before hand. If you expect the site to grow, and grow rapidly, you need to opt for the right technology.

  • JD

    a couple things i would change (just from personal experience/preference).

    in devdrive_ads: add start_date & end_date so i wouldn’t have to manually come back in to remove an ad at the end of its run.

    in devdrive_ads_activity: remove ad_activity_month & year and add ad_activity_date. Couple of reasons for this – 1) mysql has plenty of date sql functions to be able to pull out the month/year later & 2) this would actually allow you to see your sales for July 4th instead of just the month of July in total.

    • Anonymous

      Great thoughts JD. These tweaks really add to the tutorial.

  • http://twitter.com/NIMSRULES Nirmal Shah

    Great start for an awesome tut. Waiting for the remaining part(s)

  • jacharless

    Good beginning. This approach can be used also for impression to click ratios, case rotation of geo-ads, as well hiding ads from specific IPA, click bombs, etc. Looking forward to P2.