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;
SET time_zone = '+00:00'; -- I don't want MySQL to convert to current database session timezone

CREATE TABLE organizers (
    id bigint NOT NULL,
    email varchar(90) NOT NULL,
    PRIMARY KEY(id)
);

CREATE TABLE events (
    id bigint NOT NULL,
    venue text,
    headline varchar(200),
    registration_type enum('free', 'paid') NOT NULL DEFAULT 'free',
    organizer_id bigint NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (organizer_id) REFERENCES organizers(id) ON DELETE CASCADE
);

CREATE TABLE event_talks (
    event_id bigint NOT NULL,
    talk_id bigint NOT NULL,
    PRIMARY KEY (event_id, talk_id),
    FOREIGN KEY (talk_id) REFERENCES talks(id) ON DELETE CASCADE,
    FOREIGN KEY (event_id) REFERENCES events(id) ON DELETE CASCADE
);

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


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


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

CREATE TABLE attendee_accounts (
    id bigint NOT NULL,
    email varchar(90) NOT NULL, -- useful data redundancy
    password varchar(180) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (id) REFERENCES attendees(id) ON DELETE CASCADE
);

CREATE TABLE organizer_accounts (
    id bigint NOT NULL,
    email varchar(90) NOT NULL, -- useful data redundancy
    password varchar(180) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY (id) REFERENCES organizers(id) ON DELETE CASCADE
);


CREATE TABLE schedules (
    id bigint NOT NULL,
    talk_id bigint NOT NULL,
    start_time TIMESTAMP NOT NULL,
    speaker_id bigint NULL,
    end_time TIMESTAMP NOT NULL,
    location_id bigint NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (talk_id) REFERENCES talks(id) ON DELETE CASCADE,
    FOREIGN KEY (speaker_id) REFERENCES speakers(id) ON DELETE SET NULL,
    FOREIGN KEY (location_id) REFERENCES locations(id) ON DELETE SET NULL
);


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
);

CREATE TABLE locations (
    id bigint NOT NULL,
    name varchar(200) NOT NULL,
    code char(6) NOT NULL,
    PRIMARY KEY (id),
    UNIQUE(code)
); 

QUERIES

• Full speaker talk agenda for an specific event with id = 3

SELECT s.start_time, t.title, sp.name AS speaker_fullname,
l.name
FROM schedules s
JOIN talks t ON s.talk_id = t.id
JOIN event_talks esp ON t.id = esp.talk_id
JOIN location l ON s.location_id = l.id
ORDER BY s.start_time WHERE esp.event_id = 3;

• List of attendees for a talk with id = 1 linked to a specific event with id = 2

SELECT a.name
FROM attendees a
JOIN registrations r ON a.id = r.attendee_id
JOIN event_talks esp ON r.talk_id = esp.talk_id
ORDER BY a.registration_date 
WHERE r.talk_id = 1 AND esp.event_id = 2;