Designing an ad banner tracker

Question

I'm creating an ad banner tracker for the ad banners on my website by using PHP. The tracker would track unique impressions, total impressions, unique clicks and total clicks of each banner.

The method I would use for detecting unique impressions/clicks is using IP. I'm thinking that the following four tables would be needed:

• Banner - For storing information of each banner.
• Unique Impression Information - Mainly for storing IPs of visitors who view the banners.
• Unique Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of unique visitors who clicked the banners.
• Total Click Information - For storing detailed information (IP, country, device, browser, operation system, etc) of every visitors who clicked the banners.

For storing the counts of impressions and clicks, I would use these four columns of the Banner table: "Unique Impression", "Total Impression", "Unique Click" and "Total Click".

The logic of counting impressions (or clicks) would be like:

• If a visitor's IP is not found in the database, the IP would be stored in the database and the visitor would increase the values of both the Unique and Total Impression (or Click) columns of the Banner table by 1.
• If a visitor's IP is found in the database, the visitor would only increase the value of the Total Impression (or Click) column of the Banner table again by 1.

I for now have no desire to know detailed information of visitors who only view but don't click the banners. That's why the visitor information stored in the Unique Impression Informaion table is only IP.

Advice on the efficiency of such a database design would be much appreciated.

== Update ==

The CREATE TABLE statements would be:

CREATE TABLE `Banner`
(
    `ID` int(11) NOT NULL AUTO_INCREMENT,
    `Unique Impression` int(15) NOT NULL,
    `Total Impression` int(15) NOT NULL,
    `Unique Click` int(15) NOT NULL,
    `Total Click` int(15) NOT NULL,
    /* Columns like `Name`, `Url`, `Src`, etc are omitted at here */
    PRIMARY KEY (ID)
);

/*
CREATE TABLE `Unique Impression IP` */ -- before changing table name
CREATE TABLE `Unique Impression Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    PRIMARY KEY (ID)
);

CREATE TABLE `Unique Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

CREATE TABLE `Total Click Information`
(
    `ID` int(15) NOT NULL AUTO_INCREMENT,
    `IP` VARCHAR(255) NOT NULL,
    `Banner ID` int(11) NOT NULL,
    /* Columns like `Country`, `Device`, `Browser`, etc are omitted at here */
    PRIMARY KEY (ID)
);

As an example, the MySQL quies that will be used for detecting unique impressions would be:

$check_ip = mysqli_query($conn, "SELECT `IP`, `Banner ID` from `Unique Impression Information` WHERE `IP` = '$visitor_ip' AND `Banner ID` = '$banner_id'");

if (mysqli_num_rows($check_ip) === 0)
{
    mysqli_query($conn, "INSERT INTO `Unique Impression IP` (`IP`, `Banner ID`) VALUES ('$visitor_ip', '$banner_id')");
    mysqli_query($conn, "UPDATE `Banner` SET `Unique Impression` = `Unique Impression` + 1, `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}
else
{
    mysqli_query($conn, "UPDATE `Banner` SET `Total Impression` = `Total Impression` + 1 WHERE `ID` = '$banner_id'");
}

Answer 1

For Unique Impression IP as there is no need for a autoincrement primary key, use VARBINARY(16) for IP address, and as the primary key. Use INET6_ATON() when inserting it to keep it in a compact form.

Use:

INSERT IGNORE INTO `Unique Impression IP` VALUES (INET6_ATON($var))

And look at rows affected to see if that exists or not for your UPDATE Banner statement branch. As it's a unique key (primary keys are unique) it will only be inserted once. This removes a race condition in your current code.