Storage & Schema Design

1. Why Schema Matters

A bad schema means slow queries, wasted disk, and frustrated dashboard users. Analytics queries are read-heavy and aggregate-heavy. The schema must optimize for GROUP BY, date ranges, and COUNT/AVG operations.

Consider the difference between a well-designed analytics schema and a naive one. A single pageviews table with no indexes on server_timestamp will require a full table scan every time someone loads the dashboard for "page views in the last 7 days." With a million rows, that query takes seconds. With ten million rows, it takes minutes. With proper indexes and partitioning, it takes milliseconds regardless of table size.

The schema decisions you make at the start of a project are among the hardest to change later. Adding an index is easy. Splitting a table, changing a primary key, or migrating from VARCHAR to ENUM requires downtime, data migration scripts, and careful testing. Get it right the first time.

2. Core Tables Overview

The analytics database uses six tables. Each serves a distinct purpose in the pipeline:

3. The pageviews Table

This is the primary fact table — the central table that most dashboard queries will hit. Every page load produces exactly one row here. It captures the data the collector sends in its initial beacon plus the enrichment fields added by the server.

CREATE TABLE pageviews (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    url             VARCHAR(2048) NOT NULL,
    type            VARCHAR(32) NOT NULL DEFAULT 'pageview',
    user_agent      VARCHAR(512),
    viewport_width  SMALLINT UNSIGNED,
    viewport_height SMALLINT UNSIGNED,
    referrer        VARCHAR(2048),
    client_timestamp BIGINT,
    server_timestamp DATETIME NOT NULL,
    client_ip       VARCHAR(45),
    session_id      VARCHAR(36) NOT NULL,
    payload         JSON,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_server_ts (server_timestamp),
    INDEX idx_session   (session_id),
    INDEX idx_url_ts    (url(255), server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

4. The events Table

Custom events come from collector plugins — click tracking, scroll depth measurement, form submission monitoring, and any other interaction you want to capture. Each event is a discrete action that happened on a page, linked to a session.

CREATE TABLE events (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id       VARCHAR(36) NOT NULL,
    event_name       VARCHAR(128) NOT NULL,
    event_category   VARCHAR(128),
    event_data       JSON,
    url              VARCHAR(2048),
    server_timestamp DATETIME NOT NULL,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_session    (session_id),
    INDEX idx_event_name (event_name),
    INDEX idx_server_ts  (server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

The event_data JSON column is the key design decision here. Rather than creating separate columns for every possible event payload (click x/y coordinates, scroll depth percentage, form field name), a JSON column keeps the schema stable while allowing each event type to carry different data. Dashboard queries can use MySQL's JSON_EXTRACT() function to pull specific values when needed.

5. The errors Table

JavaScript errors captured by the collector's error tracking module. Each row represents a single error occurrence on a visitor's browser. This table powers the "Errors" section of the dashboard, showing error frequency, affected pages, and browser distributions.

CREATE TABLE errors (
    id               INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id       VARCHAR(36) NOT NULL,
    error_message    VARCHAR(1024),
    error_source     VARCHAR(2048),
    error_line       INT UNSIGNED,
    error_column     INT UNSIGNED,
    stack_trace      TEXT,
    url              VARCHAR(2048),
    user_agent       VARCHAR(512),
    server_timestamp DATETIME NOT NULL,
    created_at       DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_session   (session_id),
    INDEX idx_server_ts (server_timestamp),
    INDEX idx_message   (error_message(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. The performance Table

Navigation Timing metrics and Core Web Vitals collected by the performance timing and web vitals modules. Each row corresponds to a single page load and captures the full timing breakdown.

CREATE TABLE performance (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    session_id          VARCHAR(36) NOT NULL,
    url                 VARCHAR(2048),
    ttfb                FLOAT,
    dom_content_loaded  FLOAT,
    dom_complete        FLOAT,
    load_time           FLOAT,
    lcp                 FLOAT,
    cls                 FLOAT,
    inp                 FLOAT,
    fcp                 FLOAT,
    transfer_size       INT UNSIGNED,
    resource_count      SMALLINT UNSIGNED,
    server_timestamp    DATETIME NOT NULL,
    created_at          DATETIME DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_session   (session_id),
    INDEX idx_server_ts (server_timestamp),
    INDEX idx_url_ts    (url(255), server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

All timing columns use FLOAT because sub-millisecond precision matters for Web Vitals (CLS values like 0.042 are common). FLOAT provides adequate precision for timing values while using only 4 bytes per column — half the size of DOUBLE.

7. The sessions Table

The sessions table stores aggregated visit data computed by the sessionization module. Rather than recalculating session-level metrics from raw pageviews every time the dashboard loads, this table pre-computes them. It is updated incrementally as new beacons arrive for an active session.

CREATE TABLE sessions (
    session_id       VARCHAR(36) PRIMARY KEY,
    first_page       VARCHAR(2048),
    last_page        VARCHAR(2048),
    page_count       SMALLINT UNSIGNED DEFAULT 1,
    start_time       DATETIME NOT NULL,
    last_activity    DATETIME NOT NULL,
    duration_seconds INT UNSIGNED DEFAULT 0,
    referrer         VARCHAR(2048),
    user_agent       VARCHAR(512),

    INDEX idx_start_time    (start_time),
    INDEX idx_last_activity (last_activity)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Notice that session_id is the primary key, not an auto-increment integer. This is because session IDs are generated by the server-processing pipeline (as UUIDs) and must be referenced by other tables before the session row may even exist. Using the UUID directly as the PK avoids a lookup to find the auto-increment ID.

8. The users Table

This table has nothing to do with analytics visitors. It stores dashboard login accounts — the people who log into your analytics dashboard to view reports. It is a standard authentication table with role-based access control.

CREATE TABLE users (
    id             INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email          VARCHAR(255) NOT NULL UNIQUE,
    password_hash  VARCHAR(255) NOT NULL,
    display_name   VARCHAR(128),
    role           ENUM('owner', 'admin', 'viewer') NOT NULL DEFAULT 'viewer',
    created_at     DATETIME DEFAULT CURRENT_TIMESTAMP,
    last_login     DATETIME
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

9. Indexes for Analytics

Without indexes, every query performs a full table scan. For a pageviews table with millions of rows, that means reading every row from disk to answer a simple question like "how many page views happened yesterday." Indexes are the single most important performance optimization for analytics databases.

How Indexes Work (Briefly)

An index is a sorted copy of one or more columns stored in a B-tree structure. When MySQL receives a query with a WHERE clause on an indexed column, it traverses the B-tree to find matching rows in O(log n) time instead of scanning all n rows. For a table with 10 million rows, that is the difference between examining ~23 rows (log2 of 10M) versus 10 million rows.

Covering Indexes

A covering index contains all the columns a query needs, so MySQL can answer the query entirely from the index without touching the table data. This is the fastest possible query execution.

-- This query needs: server_timestamp (WHERE), url (SELECT), COUNT (aggregate)
-- A covering index on (server_timestamp, url) handles it entirely from the index:
CREATE INDEX idx_ts_url ON pageviews (server_timestamp, url(255));

SELECT url, COUNT(*) as views
FROM pageviews
WHERE server_timestamp BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY url
ORDER BY views DESC;

Key Indexes per Table

Verifying Index Usage with EXPLAIN

Always verify that MySQL actually uses your indexes. The EXPLAIN statement shows the query execution plan:

EXPLAIN SELECT url, COUNT(*) as views
FROM pageviews
WHERE server_timestamp BETWEEN '2024-01-01' AND '2024-01-07'
GROUP BY url;

-- Look for:
--   type: range (using index for range scan, good)
--   key: idx_server_ts (your index is being used)
--   rows: 8432 (estimated rows examined, should be << total rows)
--
-- Red flags:
--   type: ALL (full table scan, no index used)
--   key: NULL (no index selected)
--   rows: 5000000 (examining most of the table)

10. Normalization vs Denormalization

In a traditional application database, you would normalize aggressively: extract user_agent strings into a separate user_agents table with a foreign key, store URLs in a urls table, and reference them by ID. This eliminates data duplication and keeps the database compact.

Analytics schemas take a different approach. They are often intentionally denormalized for read performance.

Why Denormalize?

When to Normalize

Normalization still makes sense in specific cases:

• User-Agent strings: At high volume, the same 200-character UA string appears thousands of times. Extracting to a user_agents table with a SMALLINT FK can save significant disk space.
• URLs: If your site has a small number of pages (under 1,000), a URL lookup table reduces storage and makes URL renaming easier.
• Geographic data: IP-to-country/city mappings naturally belong in a separate lookup table.

11. Data Types

Choosing the right MySQL data type for each column affects storage efficiency, query performance, and data integrity. Here are the key decisions for an analytics schema:

VARCHAR vs TEXT

Use VARCHAR when you know the maximum length and want to enforce it at the database level. Use TEXT for columns like stack_trace where the content length is unpredictable and you do not need to index the full value.

INT vs BIGINT

The client_timestamp column uses BIGINT because JavaScript's Date.now() returns milliseconds since epoch (values like 1706745600000), which exceeds INT's 4.3 billion maximum.

DATETIME vs TIMESTAMP

Use DATETIME for analytics timestamps. The TIMESTAMP type's 2038 limit is a real concern for data you plan to retain long-term, and the automatic timezone conversion can cause confusion when querying across timezones. Store everything in UTC and handle timezone conversion in the dashboard application layer.

JSON Column Type

MySQL 5.7+ supports a native JSON column type. It validates that inserted data is valid JSON, supports indexing via generated columns, and provides functions like JSON_EXTRACT(), JSON_UNQUOTE(), and ->> (shorthand for extract + unquote).

-- Extract a value from the JSON payload column:
SELECT payload->>'$.browser' AS browser
FROM pageviews
WHERE server_timestamp > '2024-01-01';

-- Create a generated column + index for frequent JSON queries:
ALTER TABLE events
ADD COLUMN scroll_depth INT
    GENERATED ALWAYS AS (JSON_EXTRACT(event_data, '$.scroll_pct')) VIRTUAL,
ADD INDEX idx_scroll_depth (scroll_depth);

Character Set: utf8mb4

Always use utf8mb4, not utf8. MySQL's utf8 is a legacy alias that only supports 3-byte characters, which cannot store emoji or some CJK characters. utf8mb4 is true UTF-8 with full 4-byte support. User-Agent strings, URLs, and page titles can all contain characters that require 4 bytes.

ENUM for Bounded Values

Use ENUM for columns with a small, fixed set of possible values. MySQL stores ENUM values as 1-2 byte integers internally, making them more storage-efficient and type-safe than VARCHAR:

-- ENUM for user roles (3 possible values, stored as 1 byte each):
role ENUM('owner', 'admin', 'viewer') NOT NULL DEFAULT 'viewer'

-- ENUM for beacon types (if your collector uses a fixed set):
type ENUM('pageview', 'spa_navigation', 'event', 'error', 'performance')

12. Partitioning by Date

As your analytics tables grow beyond millions of rows, even indexed queries can slow down because MySQL must scan a large index tree. RANGE partitioning on the server_timestamp column solves this by physically splitting the table into separate partitions, one per month (or week, or day).

How Partitioning Helps

When MySQL receives a query with a WHERE server_timestamp BETWEEN ... clause, it performs partition pruning — it skips partitions that cannot contain matching rows. A query for "last 7 days" on a table with 12 monthly partitions only scans 1 partition instead of all 12. This is automatic and requires no changes to your queries.

CREATE TABLE pageviews (
    id              INT UNSIGNED AUTO_INCREMENT,
    url             VARCHAR(2048) NOT NULL,
    type            VARCHAR(32) NOT NULL DEFAULT 'pageview',
    user_agent      VARCHAR(512),
    viewport_width  SMALLINT UNSIGNED,
    viewport_height SMALLINT UNSIGNED,
    referrer        VARCHAR(2048),
    client_timestamp BIGINT,
    server_timestamp DATETIME NOT NULL,
    client_ip       VARCHAR(45),
    session_id      VARCHAR(36) NOT NULL,
    payload         JSON,
    created_at      DATETIME DEFAULT CURRENT_TIMESTAMP,

    PRIMARY KEY (id, server_timestamp),
    INDEX idx_session (session_id),
    INDEX idx_url_ts  (url(255), server_timestamp)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
PARTITION BY RANGE (TO_DAYS(server_timestamp)) (
    PARTITION p2024_01 VALUES LESS THAN (TO_DAYS('2024-02-01')),
    PARTITION p2024_02 VALUES LESS THAN (TO_DAYS('2024-03-01')),
    PARTITION p2024_03 VALUES LESS THAN (TO_DAYS('2024-04-01')),
    PARTITION p2024_04 VALUES LESS THAN (TO_DAYS('2024-05-01')),
    PARTITION p2024_05 VALUES LESS THAN (TO_DAYS('2024-06-01')),
    PARTITION p2024_06 VALUES LESS THAN (TO_DAYS('2024-07-01')),
    PARTITION p_future  VALUES LESS THAN MAXVALUE
);

Data Retention with DROP PARTITION

The biggest operational advantage of partitioning is data retention. When you need to delete old data, dropping a partition is nearly instantaneous, while a DELETE statement on millions of rows is painfully slow and generates enormous redo logs:

-- Delete 3 months of data in milliseconds:
ALTER TABLE pageviews DROP PARTITION p2024_01, p2024_02, p2024_03;

-- Add a new partition for next month:
ALTER TABLE pageviews REORGANIZE PARTITION p_future INTO (
    PARTITION p2024_07 VALUES LESS THAN (TO_DAYS('2024-08-01')),
    PARTITION p_future VALUES LESS THAN MAXVALUE
);

Tutorial Modules