How to Design a Database Schema for an Event Ticket App

Introduction

In this tutorial, we’ll design a realistic SQL database schema for an event management and ticket application — similar to Eventbrite or Ticketmaster.

Our system will support:

• Organizers creating and managing events
• Users buying tickets
• Venues hosting events
• Payments and ticket validation
• Categories and tagging for events

By the end, you’ll have a ready-to-use schema design, SQL DDL script, and a clear understanding of how to extend it for a production-ready system.

Step 1: Identify Entities

Let’s break down the main tables and their purpose:

Table	Description
users	Stores event-goers and organizers.
organizers	Represents verified event organizers (linked to users).
venues	Stores event locations with address and capacity.
events	Core table for all events, linked to organizers and venues.
categories	Defines event types (e.g., Music, Tech, Sports).
tickets	Individual tickets purchased by users.
ticket_types	Defines ticket classes (e.g., VIP, Standard, Early Bird).
payments	Tracks completed or pending ticket payments.
attendees	Connects users to events they are attending (validated entries).
reviews	Feedback from attendees after an event.

Step 2: Define Relationships

Relationship	Type	Description
Users → Organizers	1:1	Each organizer is a user with additional profile details.
Organizers → Events	1:N	An organizer can create multiple events.
Venues → Events	1:N	Each venue can host multiple events, but each event happens at one venue.
Categories → Events	1:N	Each event belongs to one category, but a category can have many events.
Events → Ticket Types	1:N	Each event can define several ticket types (e.g., VIP, Standard).
Ticket Types → Tickets	1:N	Each ticket type can generate multiple individual tickets.
Users → Tickets	1:N	A user can purchase multiple tickets.
Users → Payments	1:N	A user can have multiple payment records (one per purchase).
Events → Attendees (Users)	M:N	Many users can attend many events.
Events → Reviews (Users)	M:N	Many users can leave reviews on many events.

Step 3: Create Database Schema &Tables

In your DBMS (Database Management System) Create the database and tables.

CREATE DATABASE event_ticket_management_db;
USE event_ticket_management_db;

CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE organizers (
    organizer_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    organization_name VARCHAR(150),
    verified BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE venues (
    venue_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    address TEXT NOT NULL,
    city VARCHAR(100),
    country VARCHAR(100),
    capacity INT,
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8)
);

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    organizer_id INT NOT NULL,
    venue_id INT NOT NULL,
    category_id INT,
    title VARCHAR(150) NOT NULL,
    description TEXT,
    start_time DATETIME NOT NULL,
    end_time DATETIME NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (organizer_id) REFERENCES organizers(organizer_id),
    FOREIGN KEY (venue_id) REFERENCES venues(venue_id),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

CREATE TABLE ticket_types (
    ticket_type_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    name VARCHAR(50),
    price DECIMAL(10, 2) NOT NULL,
    quantity INT NOT NULL,
    FOREIGN KEY (event_id) REFERENCES events(event_id)
);

CREATE TABLE tickets (
    ticket_id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_type_id INT NOT NULL,
    user_id INT NOT NULL,
    purchase_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status ENUM('ACTIVE','USED','CANCELLED') DEFAULT 'ACTIVE',
    FOREIGN KEY (ticket_type_id) REFERENCES ticket_types(ticket_type_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE payments (
    payment_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('CARD','PAYPAL','APPLE_PAY','GOOGLE_PAY'),
    payment_status ENUM('PENDING','COMPLETED','FAILED') DEFAULT 'PENDING',
    payment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE attendees (
    attendee_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    check_in_time DATETIME,
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

CREATE TABLE reviews (
    review_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    user_id INT NOT NULL,
    rating INT CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (event_id) REFERENCES events(event_id),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Step 4: ER Diagram Overview

Step 5: Example SQL Queries

List all upcoming events in the next 30 days

SELECT 
    e.title AS event_name,
    e.start_time,
    o.organization_name AS organizer
FROM events e
JOIN organizers o ON e.organizer_id = o.organizer_id
WHERE e.start_time BETWEEN NOW() AND DATE_ADD(NOW(), INTERVAL 30 DAY)
ORDER BY e.start_time ASC;

Count the number of tickets sold for each event

SELECT 
    e.title AS event_name,
    COUNT(t.ticket_id) AS tickets_sold
FROM events e
JOIN ticket_types tt ON e.event_id = tt.event_id
JOIN tickets t ON tt.ticket_type_id = t.ticket_type_id
GROUP BY e.event_id, e.title
ORDER BY tickets_sold DESC;

Find total revenue generated by each event

SELECT
e.title AS event_name,
SUM(tt.price) AS total_revenue
FROM events e
JOIN ticket_types tt ON e.event_id = tt.event_id
JOIN tickets t ON tt.ticket_type_id = t.ticket_type_id
WHERE t.status = 'ACTIVE'
GROUP BY e.event_id, e.title
ORDER BY total_revenue DESC;

Show the most popular categories by number of attendees

SELECT 
    c.name AS category_name,
    COUNT(a.attendee_id) AS total_attendees
FROM attendees a
JOIN events e ON a.event_id = e.event_id
JOIN categories c ON e.category_id = c.category_id
GROUP BY c.name
ORDER BY total_attendees DESC;

Find the top 3 highest-rated events

SELECT 
    e.title AS event_name,
    ROUND(AVG(r.rating), 2) AS avg_rating,
    COUNT(r.review_id) AS total_reviews
FROM reviews r
JOIN events e ON r.event_id = e.event_id
GROUP BY e.event_id, e.title
HAVING COUNT(r.review_id) > 0
ORDER BY avg_rating DESC
LIMIT 3;

Summary

In this tutorial, We’ve designed a complete Event Ticketing App schema using MySQL — covering entities, relationships, and SQL queries to manage events, venues, tickets, and payments.

This schema is flexible enough to extend with new features like QR ticket validation, discount codes, or multi-day events.

For hands-on learning, try building the ER diagram and running the sample queries on your local MySQL setup.

Other Schemas

Read our guide on How to Design a Database Schema for a Task Manager App to learn more database design fundamentals.

Resources

Download the complete schema from our SQL Examples repository on GitHub.

Create and edit your own Task Manager ER diagram using DrawSQL, a free web-based tool for designing relational databases.

Download MySQL Workbench, a visual tool to design, model, and manage MySQL databases locally.