Crowsfoot

Event ticketing database schema: ER diagram and SQL

Ticketing shows a chain of one-to-many relationships: a venue hosts events, an event offers ticket types, and each ticket type sells many tickets. It is a good model for conferences, concerts, and meetups.

Open this schema in the editor Jump to the SQL
venues PK id bigint name varchar city varchar capacity int attendees PK id bigint name varchar email varchar events PK id bigint FK venue_id bigint title varchar starts_at timestamp ticket_types PK id bigint FK event_id bigint name varchar price numeric quantity int tickets PK id bigint FK ticket_type_id bigint FK attendee_id bigint purchased_at timestamp checked_in boolean

Tables in this schema

venues

Places that host events.

attendees

People who buy tickets.

events

A scheduled event at one venue.

ticket_types

Kinds of ticket for an event, such as “General” or “VIP”, with a price and stock.

tickets

One sold ticket for one attendee. checked_in is flipped at the door.

How the tables relate

  • events.venue_id references venues.id: one row in venues can have many rows in events.
  • ticket_types.event_id references events.id: one row in events can have many rows in ticket_types.
  • tickets.ticket_type_id references ticket_types.id: one row in ticket_types can have many rows in tickets.
  • tickets.attendee_id references attendees.id: one row in attendees can have many rows in tickets.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE venues (
  id bigint PRIMARY KEY,
  name varchar(160) NOT NULL,
  city varchar(80) NOT NULL,
  capacity int NOT NULL
);

CREATE TABLE attendees (
  id bigint PRIMARY KEY,
  name varchar(120) NOT NULL,
  email varchar(255) NOT NULL
);

CREATE TABLE events (
  id bigint PRIMARY KEY,
  venue_id bigint NOT NULL REFERENCES venues (id),
  title varchar(200) NOT NULL,
  starts_at timestamptz NOT NULL
);

CREATE TABLE ticket_types (
  id bigint PRIMARY KEY,
  event_id bigint NOT NULL REFERENCES events (id),
  name varchar(80) NOT NULL,
  price numeric(10,2) NOT NULL,
  quantity int NOT NULL
);

CREATE TABLE tickets (
  id bigint PRIMARY KEY,
  ticket_type_id bigint NOT NULL REFERENCES ticket_types (id),
  attendee_id bigint NOT NULL REFERENCES attendees (id),
  purchased_at timestamptz NOT NULL,
  checked_in boolean NOT NULL DEFAULT false
);

Design notes

  • Selling out: compare ticket_types.quantity with the count of its tickets inside a transaction to avoid overselling.
  • Storing the price paid on the ticket (not just on the type) protects history if prices change.
  • A ticket belongs to a type, and the type to an event, so you rarely need to store event_id on tickets.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.