Как адаптировать модель данных под новые требования, например, для внедрения реферальной программы?

При внедрении реферальной программы я расширяю существующую модель данных, стремясь к минимальному вмешательству и сохранению целостности.

Шаги адаптации:

1. Анализ существующей модели: Определяю, где логичнее всего хранить реферальные связи. Чаще всего это таблица users. Если в ней уже есть связи (например, manager_id), можно использовать аналогичный паттерн.

2. Проектирование изменений:

   • Вариант 1 (простой): Добавляю в таблицу пользователей поле referrer_id, ссылающееся на users.id. Подходит для простых программ "один реферер".

     ALTER TABLE users ADD COLUMN referrer_id INT NULL;
     ALTER TABLE users ADD CONSTRAINT fk_user_referrer 
     FOREIGN KEY (referrer_id) REFERENCES users(id);

   • Вариант 2 (гибкий): Создаю отдельную таблицу referrals для хранения истории приглашений. Это позволяет отслеживать несколько попыток, статусы и метаданные.

     CREATE TABLE referrals (
     id BIGSERIAL PRIMARY KEY,
     referrer_user_id INT NOT NULL REFERENCES users(id),
     referred_user_id INT NOT NULL UNIQUE REFERENCES users(id), -- один пользователь - одно приглашение
     referral_code VARCHAR(32) NOT NULL UNIQUE, -- код приглашения
     status VARCHAR(20) NOT NULL DEFAULT 'pending', -- pending, completed, expired
     created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
     completed_at TIMESTAMPTZ NULL
     );
     CREATE INDEX idx_referrals_referrer ON referrals(referrer_user_id);
     CREATE INDEX idx_referrals_referred ON referrals(referred_user_id);

3. Обеспечение целостности и логики:

   • Добавляю CHECK-ограничения, чтобы referrer_id не мог быть равен id самого пользователя.
   • На уровне приложения или с помощью триггера в БД реализую бизнес-правила (например, начисление бонусов при смене статуса реферала на completed).

4. Миграция данных: Пишу одноразовый скрипт (например, на SQL или в миграции Alembic/Liquibase) для переноса существующих реферальных связей, если они есть в логах или других системах.

5. Оптимизация: Индексы на поля, участвующие в джойнах (referrer_id, referred_user_id), обязательны. Для анализа эффективности программы добавляю в витрину данных агрегированные метрики по рефералам.