A database schema for an event plan and management app that is Meetup-like using either MySQL, SQLite or PostgreSQL as primary database

mid_budget_event_plan_and_management_app.sql

-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci;  -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE speakers (
    id bigint NOT NULL,
    name varchar(255) NOT NULL,
    bio text,
    email varchar(90) UNIQUE NOT NULL,
    photo_url varchar(255),
    PRIMARY KEY (id)
);


CREATE TABLE talks (
    id bigint NOT NULL,
    title varchar(255) NOT NULL,
    full_description mediumtext,
    abstract text,
    speaker_id bigint NOT NULL,
    duration int NOT NULL, -- useful data redundancy for `start_time` and `end_time` in schedules table  
    PRIMARY KEY (id),
    FOREIGN KEY (speaker_id) REFERENCES speakers(id) ON DELETE SET NULL
);


CREATE TABLE attendees (
    id bigint NOT NULL,
    name varchar(255) NOT NULL,
    email varchar(90) UNIQUE NOT NULL,
    registration_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);


CREATE TABLE schedules (
    id bigint NOT NULL,
    talk_id bigint NOT NULL,
    start_time timestamp NOT NULL,
    end_time timstamp NOT NULL,
    location varchar(255),
    PRIMARY KEY (id),
    FOREIGN KEY (talk_id) REFERENCES talks(id) ON DELETE CASCADE
);


CREATE TABLE registrations (
    id bigint NOT NULL,
    attendee_id bigint NOT NULL,
    talk_id bigint NOT NULL,
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    FOREIGN KEY (attendee_id) REFERENCES attendees(attendee_id) ON DELETE CASCADE,
    FOREIGN KEY (talk_id) REFERENCES talks(talk_id) ON DELETE CASCADE,
    UNIQUE(attendee_id, talk_id) -- prevents duplicate registration
);

QUERIES

• Full event agenda

SELECT s.start_time, t.title, sp.name AS speakers
FROM schedules s
JOIN talks t ON s.talk_id = t.id
JOIN speakers sp ON t.speaker_id = sp.id
ORDER BY s.start_time;

• List of attendees for a talk with id = 1

SELECT a.name
FROM attendees a
JOIN registrations r ON a.id = r.attendee_id
WHERE r.talk_id = 1;