Table of Contents

Database Schema & SQL Setup

Traffic Elf uses a MySQL database named: 

trafficelf

A dedicated database user is used for application access: 

  Username: tedbeditor

  Development Pass: !0meg4#

Database Creation

CREATE DATABASE trafficelf
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

User Access

(Assumes user already exists) 

GRANT ALL PRIVILEGES ON trafficelf.*
TO 'tedbeditor'@'localhost';
 
FLUSH PRIVILEGES;

Clients Table

Stores advertiser and client information.

Columns: 

CREATE TABLE clients (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    address TEXT,
    phone VARCHAR(50),
    contact_name VARCHAR(255),
    contact_title VARCHAR(255),
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Campaigns Table

Stores advertising campaigns associated with clients or agencies.

Columns: 

CREATE TABLE campaigns (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    owner VARCHAR(255),
    notes TEXT,
    agency VARCHAR(255),
    start_date DATE,
    end_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Notes