event-calendar-schema Replay the feed. Never a duplicate.

Why it exists

The DJ Calendar tracks thousands of shows pulled from Bandsintown and Ticketmaster. Those feeds don't hand you stable IDs you can trust across sources. They hand you names: an artist, a venue, a city, a start time. And they arrive again and again, on a schedule, sometimes two runs at once.

So the job of this schema is simple to say and easy to get wrong: the same show must always land as the same row, no matter how many times it's ingested, which worker gets there first, or whether the process died halfway through the last run.

The dedupe contract

The database is the dedupe authority, not application memory. Artists are unique on a casefolded, diacritic-stripped name_normalized. Venues are unique on (name, city, country), because there's a Fillmore in more than one city. And events carry the key that makes everything else work:

CREATE TABLE events (
  id              BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  artist_id       INT UNSIGNED    NOT NULL,
  venue_id        INT UNSIGNED    DEFAULT NULL,     -- NULL until the venue resolves
  event_date      DATETIME        NOT NULL,         -- show start, normalized to UTC
  timezone        VARCHAR(64)     DEFAULT NULL,     -- IANA zone of the venue
  is_active       TINYINT(1)      NOT NULL DEFAULT 1, -- soft delete only
  -- one artist + one venue + one start time = one row
  UNIQUE KEY uq_events_natural (artist_id, venue_id, event_date),
  KEY idx_events_upcoming     (is_active, event_date),
  KEY idx_events_artist_date  (artist_id, event_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Trimmed from schema.sql on GitHub, where every decision is documented inline.

The race the demo shows

The obvious way to avoid duplicates is to look first: SELECT the show, and INSERT it only if it's missing. That works until two ingestion runs overlap. Both workers run their SELECT, both see nothing, both insert. Now every fan subscribed to that artist gets the show twice.

The fix is to stop asking and let the database decide in one statement. With the UNIQUE key in place, INSERT … ON DUPLICATE KEY UPDATE is atomic: the first worker inserts, the second becomes an update, and there's no window in between for anything to slip through. Run the demo in both modes and compare the row counts.

$sql = "INSERT INTO events
            (artist_id, venue_id, source, source_event_id,
             event_date, timezone, ticket_url, is_sold_out)
        VALUES
            (:artist_id, :venue_id, :source, :source_event_id,
             :event_date, :timezone, :ticket_url, :is_sold_out)
        ON DUPLICATE KEY UPDATE
            ticket_url  = VALUES(ticket_url),
            is_sold_out = VALUES(is_sold_out),
            is_active   = 1";

// rowCount() on an upsert: 1 = insert, 2 = update, 0 = no change.
$affected = $stmt->rowCount();
if ($affected === 2) return 'updated';
if ($affected === 1) return 'inserted';
return 'unchanged';

Simplified from upsertEvent() in includes/EventRepository.php. The connection runs with ATTR_EMULATE_PREPARES off, so every statement is a true server-side prepared statement.

Every column earns its place

The rest of the schema follows the same rule: each choice exists because of a specific failure it prevents.

Soft delete only

An empty feed payload is a data problem, not an instruction to erase history. Ingestion flips is_active and never hard-deletes a show.

UTC plus a timezone column

Times are stored in UTC beside the venue's IANA zone. Storing local times is how an 8 PM show becomes a 3 AM email blast.

No index without a query

idx_events_upcoming serves the site's hottest read, the next N shows. idx_events_artist_date serves artist pages. Nothing else got one.

International names

Everything is utf8mb4, and name_normalized keeps search from breaking on diacritics. Search that fails on Tiësto is broken search.

Two tokens, two powers

Newsletter alerts use double opt-in from day one. Confirming and unsubscribing are different capabilities with different blast radii, so each gets its own 32-character token from random_bytes(16). One leaked link can never do the other's job. Try it:

Signing up twice for the same artist hits the (email, artist_id) UNIQUE key, so INSERT IGNORE keeps one row and a fresh confirm token is issued. A stale link is never the only way in.

What's in the repo

• schema.sql: the four tables, artists, venues, events and alert_subscribers, with every decision explained in comments.
• includes/EventRepository.php: the idempotent upsertEvent(), the index-shaped findUpcomingByArtist(), and the full subscribe, confirm and unsubscribe lifecycle.
• Column sets are simplified from production. The ingestion pipeline, proxy cache and admin tools aren't included. The design decisions are the real ones.

Related projects