Спроектируй БД кинотеатра с залами, событиями и сиденьями

Спроектируй БД кинотеатра с залами, событиями и сиденьями

Это отличный вопрос о проектировании реляционной БД. Давайте спроектируем полную систему кинотеатра с поддержкой бронирования мест.

Анализ требований

Система должна поддерживать:

• Кинотеатры — физические локации
• Залы — помещения в кинотеатре с определённой конфигурацией мест
• Фильмы — контент, который показывается
• События — конкретные сеансы (фильм в определённый день и время в определённом зале)
• Сидения — места в зале с нумерацией
• Бронирования — пользователь бронирует место на событие
• Цены — разные типы мест могут иметь разные цены

SQL схема БД

-- 1. Кинотеатры
CREATE TABLE cinemas (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    city VARCHAR(100) NOT NULL,
    address VARCHAR(500) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 2. Залы кинотеатра
CREATE TABLE halls (
    id UUID PRIMARY KEY,
    cinema_id UUID NOT NULL REFERENCES cinemas(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    capacity INT NOT NULL CHECK (capacity > 0),
    rows_count INT NOT NULL CHECK (rows_count > 0),
    seats_per_row INT NOT NULL CHECK (seats_per_row > 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(cinema_id, name)
);

-- 3. Типы мест (эконом, премиум, VIP и т.д.)
CREATE TABLE seat_types (
    id UUID PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE,
    description VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 4. Сидения в залах
CREATE TABLE seats (
    id UUID PRIMARY KEY,
    hall_id UUID NOT NULL REFERENCES halls(id) ON DELETE CASCADE,
    seat_type_id UUID NOT NULL REFERENCES seat_types(id) ON DELETE RESTRICT,
    row_number INT NOT NULL CHECK (row_number > 0),
    seat_number INT NOT NULL CHECK (seat_number > 0),
    is_available BOOLEAN NOT NULL DEFAULT true,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(hall_id, row_number, seat_number),
    CHECK (row_number <= (SELECT rows_count FROM halls WHERE id = hall_id)),
    CHECK (seat_number <= (SELECT seats_per_row FROM halls WHERE id = hall_id))
);

-- 5. Фильмы
CREATE TABLE films (
    id UUID PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    duration_minutes INT NOT NULL CHECK (duration_minutes > 0),
    rating VARCHAR(10),
    genre VARCHAR(100),
    release_date DATE,
    director VARCHAR(255),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 6. События (сеансы)
CREATE TABLE events (
    id UUID PRIMARY KEY,
    hall_id UUID NOT NULL REFERENCES halls(id) ON DELETE CASCADE,
    film_id UUID NOT NULL REFERENCES films(id) ON DELETE RESTRICT,
    event_start TIMESTAMPTZ NOT NULL,
    event_end TIMESTAMPTZ NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'UPCOMING',
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CHECK (event_start < event_end),
    INDEX idx_hall_datetime (hall_id, event_start),
    INDEX idx_status (status)
);

-- 7. Цены за места по типам для событий
CREATE TABLE event_seat_prices (
    id UUID PRIMARY KEY,
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
    seat_type_id UUID NOT NULL REFERENCES seat_types(id) ON DELETE RESTRICT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(event_id, seat_type_id)
);

-- 8. Пользователи
CREATE TABLE users (
    id UUID PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    phone VARCHAR(20),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- 9. Бронирования (резервации)
CREATE TABLE bookings (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE RESTRICT,
    status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE',
    total_price DECIMAL(12, 2) NOT NULL CHECK (total_price >= 0),
    booking_date TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_event (user_id, event_id),
    INDEX idx_status (status)
);

-- 10. Детали бронирования (какие места забронированы)
CREATE TABLE booking_seats (
    id UUID PRIMARY KEY,
    booking_id UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
    seat_id UUID NOT NULL REFERENCES seats(id) ON DELETE RESTRICT,
    seat_type_id UUID NOT NULL REFERENCES seat_types(id) ON DELETE RESTRICT,
    price_paid DECIMAL(10, 2) NOT NULL CHECK (price_paid >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(booking_id, seat_id),
    UNIQUE(seat_id, (SELECT event_id FROM bookings WHERE id = booking_id))
);

-- 11. Платежи
CREATE TABLE payments (
    id UUID PRIMARY KEY,
    booking_id UUID NOT NULL REFERENCES bookings(id) ON DELETE CASCADE,
    amount DECIMAL(12, 2) NOT NULL CHECK (amount >= 0),
    payment_method VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'PENDING',
    transaction_id VARCHAR(255),
    paid_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_status (status)
);

Практические SQL запросы

-- 1. Показать доступные места для события
SELECT s.id, s.row_number, s.seat_number, st.name as seat_type, esp.price
FROM seats s
JOIN seat_types st ON s.seat_type_id = st.id
JOIN event_seat_prices esp ON st.id = esp.seat_type_id AND esp.event_id = 'EVENT_ID'
WHERE s.hall_id = (SELECT hall_id FROM events WHERE id = 'EVENT_ID')
  AND s.available = true
  AND s.id NOT IN (
    SELECT bs.seat_id FROM booking_seats bs
    JOIN bookings b ON bs.booking_id = b.id
    WHERE b.event_id = 'EVENT_ID' AND b.status = 'ACTIVE'
  )
ORDER BY s.row_number, s.seat_number;

-- 2. Получить события кинотеатра на неделю
SELECT e.id, f.title, h.name as hall_name, e.event_start, e.event_end,
       COUNT(CASE WHEN bs.id IS NOT NULL THEN 1 END) as booked_seats,
       h.capacity - COUNT(CASE WHEN bs.id IS NOT NULL THEN 1 END) as available_seats
FROM events e
JOIN films f ON e.film_id = f.id
JOIN halls h ON e.hall_id = h.id
LEFT JOIN bookings b ON e.id = b.event_id AND b.status = 'ACTIVE'
LEFT JOIN booking_seats bs ON b.id = bs.booking_id
WHERE h.cinema_id = 'CINEMA_ID'
  AND e.event_start BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '7 days'
  AND e.status != 'CANCELLED'
GROUP BY e.id, f.title, h.name, h.capacity, e.event_start, e.event_end
ORDER BY e.event_start;

-- 3. Занятость каждого зала по событиям
SELECT e.id as event_id, f.title, e.event_start,
       COUNT(*) as total_seats,
       COUNT(CASE WHEN bs.id IS NOT NULL THEN 1 END) as booked_seats,
       ROUND(100.0 * COUNT(CASE WHEN bs.id IS NOT NULL THEN 1 END) / COUNT(*), 2) as occupancy_percent
FROM events e
JOIN films f ON e.film_id = f.id
JOIN seats s ON e.hall_id = s.hall_id
LEFT JOIN booking_seats bs ON s.id = bs.seat_id
LEFT JOIN bookings b ON bs.booking_id = b.id AND b.event_id = e.id AND b.status = 'ACTIVE'
WHERE e.event_start >= CURRENT_TIMESTAMP
GROUP BY e.id, f.title, e.event_start
ORDER BY e.event_start;

-- 4. Бронирования пользователя
SELECT b.id, f.title, h.name as hall_name, e.event_start,
       STRING_AGG(s.row_number || '-' || s.seat_number, ', ' ORDER BY s.row_number, s.seat_number) as seats,
       b.total_price, b.status
FROM bookings b
JOIN events e ON b.event_id = e.id
JOIN films f ON e.film_id = f.id
JOIN halls h ON e.hall_id = h.id
JOIN booking_seats bs ON b.id = bs.booking_id
JOIN seats s ON bs.seat_id = s.id
WHERE b.user_id = 'USER_ID'
GROUP BY b.id, f.title, h.name, e.event_start, b.total_price, b.status
ORDER BY e.event_start DESC;

Ключевые моменты дизайна

1. Нормализация: 3NF
2. Целостность данных: CHECK constraints, FOREIGN KEYS
3. Performance: Индексы на часто используемые поля
4. Масштабируемость: Partition by event_start if needed
5. Auditing: created_at, updated_at на каждой таблице
6. Мягкое удаление: можно добавить deleted_at для логического удаления

Diagram ERD

Cinemas (1) ---> (M) Halls
                        |
                    (1) |
                    ├---> Seats
                    |
                    └---> Events (1) ---> (M) Bookings
                            |                    |
                            |                    ├--> BookingSeats --> Seats
                            |                    |
                            |                    └--> Payments
                            |
                            └---> Films

SeatTypes ---> Seats

           ---> EventSeatPrices --> Events

Users (1) ---> (M) Bookings