Проектирование схемы БД для клиентов, счетов и платежей

5. Проектирование схемы БД для клиентов, счетов и платежей

Условие задачи:
Необходимо спроектировать реляционную схему базы данных для трех сущностей:

клиенты
счета
платежи

Нужно определить:

какие поля должны быть у каждой сущности
какие первичные и внешние ключи нужны
как связаны между собой клиенты, счета и платежи

Ожидается, что:

один клиент может иметь несколько счетов
каждый счет принадлежит одному клиенту
платежи должны быть связаны со счетами
по схеме должно быть понятно, откуда списываются и куда зачисляются деньги, если это предусмотрено логикой

Нужно составить структуру таблиц и описать связи между ними.

Спойлеры к решению

Подсказки

Между clients и accounts связь один ко многим: один клиент может иметь несколько счетов.
В таблице accounts нужен внешний ключ client_id, который ссылается на clients.id.
Платеж лучше связать сразу с двумя счетами: from_account_id и to_account_id.
from_account_id показывает, откуда списываются деньги.
to_account_id показывает, куда зачисляются деньги.
Для суммы платежа лучше использовать NUMERIC, а не FLOAT.
Для платежа желательно добавить ограничение amount > 0.
Чтобы нельзя было перевести деньги с одного счёта на тот же самый счёт, можно добавить CHECK (from_account_id <> to_account_id).

Решение

CREATE TABLE clients (
    id SERIAL PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE,
    phone VARCHAR(50),
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE accounts (
    id SERIAL PRIMARY KEY,
    client_id INTEGER NOT NULL,
    account_number VARCHAR(50) NOT NULL UNIQUE,
    balance NUMERIC(14, 2) NOT NULL DEFAULT 0,
    currency CHAR(3) NOT NULL DEFAULT 'RUB',
    status VARCHAR(30) NOT NULL DEFAULT 'active',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_accounts_client
        FOREIGN KEY (client_id)
        REFERENCES clients(id)
        ON DELETE RESTRICT
);

CREATE TABLE payments (
    id SERIAL PRIMARY KEY,
    from_account_id INTEGER NOT NULL,
    to_account_id INTEGER NOT NULL,
    amount NUMERIC(14, 2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'RUB',
    status VARCHAR(30) NOT NULL DEFAULT 'created',
    purpose TEXT,
    payment_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_payments_from_account
        FOREIGN KEY (from_account_id)
        REFERENCES accounts(id)
        ON DELETE RESTRICT,

    CONSTRAINT fk_payments_to_account
        FOREIGN KEY (to_account_id)
        REFERENCES accounts(id)
        ON DELETE RESTRICT,

    CONSTRAINT chk_payments_amount_positive
        CHECK (amount > 0),

    CONSTRAINT chk_payments_different_accounts
        CHECK (from_account_id <> to_account_id)
);

Связи в этой схеме:

clients 1 ─── N accounts

accounts 1 ─── N payments через payments.from_account_id
accounts 1 ─── N payments через payments.to_account_id

Таблица clients хранит клиентов. Таблица accounts хранит счета клиентов, каждый счёт принадлежит одному клиенту через поле client_id. Таблица payments хранит платежи между счетами: поле from_account_id показывает счёт списания, а поле to_account_id показывает счёт зачисления.

Например, если клиент переводит деньги со своего счёта на счёт другого клиента, в payments сохраняется одна запись: от какого счёта ушли деньги, на какой счёт пришли деньги, сумма, валюта, дата и статус платежа.