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...