Предложи структуру таблицы базы данных

Структура таблицы базы данных: правильный подход

Этот вопрос часто подразумевает конкретную предметную область. Я дам универсальный пример с объяснением принципов, применимых к любым таблицам.

Принципы проектирования таблиц

1. Нормализация (Normal Forms)

• 1NF: Атомарность значений (без массивов в полях)
• 2NF: Зависимость полей от первичного ключа
• 3NF: Отсутствие транзитивных зависимостей

2. Типизация

• Выбирай точный тип (не VARCHAR(1000) для всего)
• BIGINT для ID
• DECIMAL для денег
• TIMESTAMPTZ для дат

3. Ограничения (Constraints)

• PRIMARY KEY — уникальность
• FOREIGN KEY — referential integrity
• NOT NULL — обязательность
• UNIQUE — уникальность без PRIMARY KEY
• CHECK — бизнес-правила

Пример 1: Таблица пользователей (Users)

CREATE TABLE users (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    email VARCHAR(255) NOT NULL UNIQUE,
    username VARCHAR(50) NOT NULL UNIQUE,
    full_name VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
        CHECK (status IN ('active', 'inactive', 'banned', 'suspended')),
    role VARCHAR(20) NOT NULL DEFAULT 'user',
        CHECK (role IN ('admin', 'moderator', 'user')),
    email_verified BOOLEAN NOT NULL DEFAULT FALSE,
    phone VARCHAR(20),
    avatar_url VARCHAR(500),
    bio TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ,
    CONSTRAINT email_not_deleted UNIQUE NULLS NOT DISTINCT (email, deleted_at)
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_created_at ON users(created_at DESC);
CREATE INDEX idx_users_status ON users(status) WHERE deleted_at IS NULL;

Объяснение:

• id — уникальный идентификатор (BIGINT на случай больших объёмов)
• email, username — UNIQUE для быстрого поиска
• status, role — VARCHAR с CHECK для бизнес-логики
• created_at, updated_at — TIMESTAMPTZ для аудита
• deleted_at — soft delete (не физическое удаление)
• NULLS NOT DISTINCT — разрешить удаленных пользователей с одинаковым email

Пример 2: Таблица заказов (Orders)

CREATE TABLE orders (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    order_number VARCHAR(20) NOT NULL UNIQUE,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
    total_amount DECIMAL(10, 2) NOT NULL,
        CHECK (total_amount >= 0),
    tax_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    discount_amount DECIMAL(10, 2) NOT NULL DEFAULT 0,
    currency VARCHAR(3) NOT NULL DEFAULT 'USD',
    payment_status VARCHAR(20) NOT NULL DEFAULT 'pending',
    payment_method VARCHAR(50),
    shipping_address TEXT NOT NULL,
    tracking_number VARCHAR(100),
    notes TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    shipped_at TIMESTAMPTZ,
    delivered_at TIMESTAMPTZ
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

Объяснение:

• user_id — FOREIGN KEY связь с users
• ON DELETE CASCADE — если пользователь удалён, заказы тоже удалятся
• DECIMAL для денег (точность важна)
• CHECK для неотрицательных сумм
• Временные поля для отслеживания
• Составной индекс (user_id, status) для частых запросов типа "заказы юзера со статусом..."

Пример 3: Таблица товаров в заказе (Order Items)

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL,
    product_name VARCHAR(255) NOT NULL,  -- денормализация для истории
    sku VARCHAR(50) NOT NULL,
    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,
        CHECK (discount_percent >= 0 AND discount_percent <= 100),
    line_total DECIMAL(10, 2) NOT NULL,
        CHECK (line_total >= 0),
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

Объяснение:

• Отдельная таблица для разложения many-to-many связи
• product_name, sku — денормализация для истории (сохраняем что было в момент заказа)
• CHECK для валидации на уровне БД
• line_total — вычисленное поле (можно пересчитывать из quantity * unit_price)

Пример 4: Таблица для логирования изменений (Audit Log)

CREATE TABLE audit_log (
    id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    entity_type VARCHAR(50) NOT NULL,  -- 'order', 'user', 'product'
    entity_id BIGINT NOT NULL,
    action VARCHAR(20) NOT NULL,  -- 'insert', 'update', 'delete'
    old_values JSONB,
    new_values JSONB,
    changed_by BIGINT REFERENCES users(id),
    changed_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    reason TEXT
);

CREATE INDEX idx_audit_entity ON audit_log(entity_type, entity_id);
CREATE INDEX idx_audit_changed_at ON audit_log(changed_at DESC);

Ошибки, которых нужно избегать

Плохо:

-- VARCHAR для всего
CREATE TABLE orders (
    id VARCHAR(255),
    status VARCHAR(255),
    amount VARCHAR(255),  -- Деньги в VARCHAR!
    created_date VARCHAR(255)  -- Дата в VARCHAR!
);

Хорошо:

CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    status VARCHAR(20) CHECK (...),
    amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ
);

Чеклист при проектировании таблицы

• ✓ Выбран правильный тип данных для каждого поля
• ✓ PRIMARY KEY определён
• ✓ FOREIGN KEY'и связывают связанные таблицы
• ✓ Есть CHECK constraints для бизнес-правил
• ✓ UNIQUE constraints для полей, которые должны быть уникальны
• ✓ NOT NULL где нужно (не нулевые поля)
• ✓ Индексы на часто используемых полях в WHERE, JOIN, ORDER BY
• ✓ created_at, updated_at для аудита
• ✓ Таблица в 3NF (отсутствуют избыточные зависимости)
• ✓ Документация (комментарии) для сложных полей

Добавь комментарии

COMMENT ON TABLE users IS 'Таблица пользователей системы';
COMMENT ON COLUMN users.email IS 'Уникальный email, используется для входа';
COMMENT ON COLUMN users.status IS 'Статус пользователя: active/inactive/banned';

Структура таблицы определяет производительность, надёжность и scalability всей системы. Потратить время на правильный дизайн вначале — это инвестиция, которая окупается многократно.