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.