Спроектируй схему базы данных для магазина книг

Проект базы данных для магазина книг

Спроектирую реляционную базу данных (PostgreSQL) для книжного магазина, учитывая ключевые сущности: книги, авторы, категории, издания, пользователи, заказы, складские остатки и отзывы.

Основные таблицы и связи

1. Таблицы справочников

-- Авторы (пишут книги)
CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    biography TEXT,
    birth_date DATE,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Издательства
CREATE TABLE publishers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL UNIQUE,
    address TEXT,
    contact_phone VARCHAR(50),
    website VARCHAR(255)
);

-- Категории/жанры книг (иерархия через nested sets или parent_id)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_id INT REFERENCES categories(id),
    description TEXT,
    slug VARCHAR(100) UNIQUE,
    lft INT, -- для nested sets
    rgt INT
);

2. Ядро: книги и издания

-- Книги (логический продукт, независимо от издания)
CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(500) NOT NULL,
    original_title VARCHAR(500),
    description TEXT,
    publication_year SMALLINT,
    main_cover_image_url VARCHAR(500),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Издания книги (физические варианты с ISBN)
CREATE TABLE editions (
    id SERIAL PRIMARY KEY,
    book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    publisher_id INT REFERENCES publishers(id),
    isbn VARCHAR(13) UNIQUE,
    format VARCHAR(50), -- 'твердый переплет', 'мягкий', 'электронная'
    pages INT,
    language CHAR(2) DEFAULT 'ru',
    publication_date DATE,
    weight_grams INT,
    price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    discount_percent DECIMAL(5,2) DEFAULT 0 CHECK (discount_percent BETWEEN 0 AND 100),
    available_quantity INT DEFAULT 0 CHECK (available_quantity >= 0),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Связь книг и авторов (многие-ко-многим)
CREATE TABLE book_authors (
    book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    author_id INT NOT NULL REFERENCES authors(id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, author_id)
);

-- Связь книг и категорий (многие-ко-многим)
CREATE TABLE book_categories (
    book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    category_id INT NOT NULL REFERENCES categories(id) ON DELETE CASCADE,
    PRIMARY KEY (book_id, category_id)
);

3. Пользователи и заказы

-- Пользователи/клиенты
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(255),
    phone VARCHAR(50),
    address TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    registered_at TIMESTAMP DEFAULT NOW()
);

-- Корзина пользователя
CREATE TABLE cart_items (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    edition_id INT NOT NULL REFERENCES editions(id) ON DELETE CASCADE,
    quantity INT NOT NULL CHECK (quantity > 0),
    added_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(user_id, edition_id) -- одна позиция на товар в корзине
);

-- Заказы
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES users(id),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'pending', -- pending, processing, shipped, delivered, cancelled
    total_amount DECIMAL(12,2) NOT NULL CHECK (total_amount >= 0),
    delivery_address TEXT NOT NULL,
    delivery_type VARCHAR(50) DEFAULT 'courier',
    payment_method VARCHAR(50) DEFAULT 'card',
    payment_status VARCHAR(50) DEFAULT 'unpaid',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Позиции заказа
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    edition_id INT NOT NULL REFERENCES editions(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
    discount_percent DECIMAL(5,2) DEFAULT 0,
    total_price DECIMAL(12,2) GENERATED ALWAYS AS (
        quantity * unit_price * (1 - discount_percent/100)
    ) STORED
);

4. Дополнительные таблицы

-- Отзывы на книги
CREATE TABLE reviews (
    id SERIAL PRIMARY KEY,
    book_id INT NOT NULL REFERENCES books(id) ON DELETE CASCADE,
    user_id INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    rating SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment TEXT,
    is_approved BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT NOW(),
    UNIQUE(book_id, user_id) -- один отзыв на книгу от пользователя
);

-- История цен (для аналитики)
CREATE TABLE price_history (
    id SERIAL PRIMARY KEY,
    edition_id INT NOT NULL REFERENCES editions(id) ON DELETE CASCADE,
    old_price DECIMAL(10,2),
    new_price DECIMAL(10,2) NOT NULL,
    changed_at TIMESTAMP DEFAULT NOW()
);

-- Поставщики и закупки (для управления складом)
CREATE TABLE suppliers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_info TEXT
);

CREATE TABLE procurements (
    id SERIAL PRIMARY KEY,
    supplier_id INT REFERENCES suppliers(id),
    edition_id INT NOT NULL REFERENCES editions(id),
    quantity INT NOT NULL CHECK (quantity > 0),
    unit_cost DECIMAL(10,2) NOT NULL,
    procurement_date DATE DEFAULT CURRENT_DATE
);

Ключевые индексы для производительности

-- Основные индексы для частых запросов
CREATE INDEX idx_editions_book_id ON editions(book_id);
CREATE INDEX idx_editions_price ON editions(price);
CREATE INDEX idx_editions_available ON editions(available_quantity) WHERE available_quantity > 0;

CREATE INDEX idx_book_categories_category ON book_categories(category_id);
CREATE INDEX idx_book_authors_author ON book_authors(author_id);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created ON orders(created_at DESC);

CREATE INDEX idx_reviews_book ON reviews(book_id);
CREATE INDEX idx_reviews_rating ON reviews(rating);

-- Индекс для полнотекстового поиска по книгам (PostgreSQL)
CREATE INDEX idx_books_search ON books USING GIN(to_tsvector('russian', title || ' ' || COALESCE(description, '')));

Основные бизнес-правила

1. Целостность данных:

   • ISBN уникален для каждого издания
   • Количество на складе не может быть отрицательным
   • Цена всегда неотрицательная
   • Скидка в пределах 0-100%

2. Согласованность:

   • При создании заказа резервируется товар (уменьшается available_quantity)
   • Отмена заказа возвращает товар на склад
   • Пользователь может оставить только один отзыв на книгу

3. Расширяемость:

   • Поддержка мультиязычности через отдельную таблицу book_translations
   • Возможность добавления атрибутов книг через attributes и book_attributes (EAV)
   • Система промокодов через отдельную таблицу promo_codes

Пример запроса: получение популярных книг в категории

SELECT 
    b.id,
    b.title,
    a.full_name AS author,
    MIN(e.price) AS min_price,
    AVG(r.rating) AS avg_rating,
    SUM(oi.quantity) AS total_sold
FROM books b
JOIN book_authors ba ON b.id = ba.book_id
JOIN authors a ON ba.author_id = a.id
JOIN editions e ON b.id = e.book_id
LEFT JOIN reviews r ON b.id = r.book_id
LEFT JOIN order_items oi ON e.id = oi.edition_id
JOIN book_categories bc ON b.id = bc.book_id
WHERE bc.category_id = :category_id
    AND e.available_quantity > 0
GROUP BY b.id, b.title, a.full_name
ORDER BY total_sold DESC NULLS LAST, avg_rating DESC
LIMIT 20;

Эта схема покрывает основные потребности книжного магазина: каталог товаров, управление запасами, обработку заказов, учет пользователей и аналитику продаж. Архитектура масштабируема: можно добавить партицирование таблиц orders по дате, репликацию для чтения, или перейти на микросервисную архитектуру с выделенными БД для заказов и каталога.