Спроектируйте базовую схему реляционной базы данных для книжного интернет-магазина.

Ответ

Конечно. Вот пример базовой, но расширяемой схемы данных для книжного магазина.

Основные сущности:

Книги (books): Основная информация о товаре.
Авторы (authors): Информация об авторах.
Покупатели (customers): Данные зарегистрированных пользователей.
Заказы (orders): Информация о заказах.

Связующие таблицы (для отношений Many-to-Many):

book_authors: Связывает книги и авторов (у книги может быть несколько авторов, и автор может написать несколько книг).
order_items: Состав конкретного заказа.

SQL-схема (синтаксис PostgreSQL)

1. Авторы (authors)

CREATE TABLE authors (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    biography TEXT
);

2. Книги (books)

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    isbn VARCHAR(20) UNIQUE NOT NULL, -- Международный стандартный книжный номер
    description TEXT,
    price DECIMAL(10, 2) NOT NULL CHECK (price >= 0),
    stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    published_year INT
);

3. Связь Книги-Авторы (book_authors) - Many-to-Many

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) -- Составной первичный ключ
);

4. Покупатели (customers)

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL, -- Храним только хэш пароля
    registration_date TIMESTAMPTZ DEFAULT NOW()
);

5. Заказы (orders)

-- Статус заказа лучше вынести в ENUM для контроля значений
CREATE TYPE order_status AS ENUM ('pending', 'paid', 'shipped', 'delivered', 'cancelled');

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INT REFERENCES customers(id) ON DELETE SET NULL, -- Если удаляем клиента, заказы остаются
    status order_status NOT NULL DEFAULT 'pending',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    shipping_address TEXT NOT NULL
);

6. Позиции заказа (order_items)

CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    book_id INT NOT NULL REFERENCES books(id) ON DELETE RESTRICT, -- Не даем удалить книгу, если она есть в заказах
    quantity INT NOT NULL CHECK (quantity > 0),
    price_per_item DECIMAL(10, 2) NOT NULL -- Цена на момент покупки
);

Ключевые моменты и улучшения:

Отношение M2M: Для связи книг и авторов используется отдельная таблица book_authors, что является правильным подходом для отношения "многие-ко-многим".
Безопасность: Пароль пользователя хранится в виде хэша (password_hash).
Целостность данных: Используются FOREIGN KEY, NOT NULL, UNIQUE и CHECK ограничения. ON DELETE определяет поведение при удалении связанных записей.
Денормализация: В order_items дублируется цена (price_per_item). Это сделано намеренно, чтобы зафиксировать цену на момент покупки, так как цена в таблице books может измениться.
Индексы: Для ускорения запросов следует добавить индексы на внешние ключи (большинство СУБД делают это автоматически) и на часто используемые для поиска поля, такие как books.title и authors.full_name.