Проектирование базы данных CRM: от идеи до SQL-скриптов

Анализ требований и концептуальное проектирование: сущности Клиент, Договор, Оплата и Задача

Добро пожаловать на курс «Проектирование базы данных CRM: от идеи до SQL-скриптов». Это первая статья нашего цикла, в которой мы пройдем путь от абстрактной идеи «нужно где-то хранить данные» до конкретных таблиц и связей в базе данных.

Многие новички совершают одну и ту же ошибку: сразу бросаются писать код или создавать таблицы в Excel. Мы поступим иначе. Мы начнем с проектирования. Представьте, что вы архитектор: прежде чем класть кирпичи, нужно начертить план здания. В мире баз данных этот план называется схемой.

Шаг 1: Анализ предметной области

Наша цель — создать базу данных (БД) для CRM-системы (Customer Relationship Management). Давайте разберем требования, которые поставил перед нами заказчик (или мы сами):

> Спроектировать БД для учета клиентов, договоров, оплат и задач.

Из этого короткого предложения мы должны выделить ключевые сущности. Сущность — это объект реального мира, информацию о котором мы хотим хранить.

Выделяем сущности

Клиент (Client) — это центр нашей системы. Без клиентов нет бизнеса.

Договор (Contract) — юридическое подтверждение сделки с клиентом.

Оплата (Payment) — денежная транзакция, привязанная к договору.

Задача (Task) — напоминание менеджеру сделать что-то для клиента (позвонить, отправить письмо).

Определяем атрибуты

Теперь для каждой сущности нужно определить набор характеристик (атрибутов), которые нам важны.

* Клиент: Нам нужно знать, как к нему обращаться (Имя), как связаться (Email, Телефон) и когда он появился в базе (Дата регистрации). * Договор: У договора всегда есть номер, дата подписания и сумма. * Оплата: Сумма платежа и дата, когда деньги поступили. * Задача: Что нужно сделать (Описание), к какому сроку (Дедлайн) и выполнена ли она (Статус).

Шаг 2: Определение связей и Нормализация

Просто создать четыре таблицы недостаточно. Данные должны «общаться» друг с другом. Здесь в игру вступает реляционная модель.

Типы связей

Давайте подумаем, как наши сущности связаны между собой:

Клиент — Договор: Может ли у одного клиента быть несколько договоров? Да. Может ли один и тот же договор принадлежать разным клиентам? Обычно нет. Это связь «Один-ко-Многим» (One-to-Many).

Договор — Оплата: По одному договору может быть несколько платежей (например, аванс и остаток). Но один конкретный платеж всегда относится к одному договору. Это тоже связь «Один-ко-Многим».

Клиент — Задача: У менеджера может быть 10 задач по одному клиенту. Это связь «Один-ко-Многим».

!Концептуальная схема связей между сущностями CRM

Зачем нужна нормализация?

Нормализация — это процесс организации данных для уменьшения избыточности. Давайте применим её принципы к нашей задаче.

Представьте, если бы мы хранили всё в одной гигантской таблице Excel:

| Клиент | Телефон | Номер договора | Сумма договора | Дата оплаты | Сумма оплаты | | :--- | :--- | :--- | :--- | :--- | :--- | | Иван | 555-01 | А-100 | 10000 | 01.01.2023 | 5000 | | Иван | 555-01 | А-100 | 10000 | 15.01.2023 | 5000 |

Видите проблему? Мы дублируем имя «Иван», телефон и сумму договора для каждого платежа. Если Иван сменит телефон, нам придется менять его во всех строчках. Это нарушение Второй и Третьей нормальных форм.

Чтобы это исправить, мы разделяем данные на разные таблицы и связываем их через ключи:

* Primary Key (PK, Первичный ключ): Уникальный идентификатор строки (например, ID клиента). * Foreign Key (FK, Внешний ключ): Ссылка на PK другой таблицы (например, в таблице Договоров мы храним ID клиента).

Шаг 3: Физическое проектирование (SQL-скрипты)

Теперь переведем нашу концепцию на язык SQL. Мы будем использовать синтаксис, совместимый с большинством популярных СУБД (PostgreSQL, MySQL).

1. Таблица Клиентов (Clients)

Сначала создаем независимые сущности.

Разбор: * client_id SERIAL PRIMARY KEY: Уникальный номер клиента, который генерируется автоматически. * NOT NULL: Обязательное поле. Клиент без имени нам не нужен.

2. Таблица Договоров (Contracts)

Договор не может существовать без клиента, поэтому добавляем ссылку client_id.

Разбор: * DECIMAL(10, 2): Тип данных для денег. Хранит 10 цифр всего, из них 2 после запятой. Никогда не используйте FLOAT для денег из-за проблем с точностью округления! * FOREIGN KEY: Это правило говорит базе данных: «Нельзя создать договор для клиента, которого не существует в таблице Clients».

3. Таблица Оплат (Payments)

Оплата привязана к договору, а не напрямую к клиенту (так мы точно знаем, за что пришли деньги).

4. Таблица Задач (Tasks)

Задачи привязываем к клиенту. Также добавим статус выполнения.

Разбор: * TEXT: Используем для описания, так как длина может быть любой. * BOOLEAN: Идеально подходит для статуса «сделано / не сделано».

Итоги первой части

Мы с вами проделали путь от простого текстового описания до готовых SQL-инструкций. Что мы получили в итоге?

Структурированность: Данные разложены по полочкам (таблицам).

Целостность: Благодаря внешним ключам (FOREIGN KEY) мы не потеряем данные. Нельзя удалить клиента, если у него есть действующие договоры (если не настроено каскадное удаление, о чем мы поговорим в следующих статьях).

Масштабируемость: Мы можем легко добавить новые сущности, например, «Сотрудники» или «Товары», просто создав новые таблицы и связи.

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

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

Приветствую вас во второй статье курса «Проектирование базы данных CRM: от идеи до SQL-скриптов». В прошлый раз мы набросали эскиз нашей базы данных: выделили клиентов, договоры, оплаты и задачи. Мы даже написали первые SQL-скрипты. Но достаточно ли этого, чтобы система работала надежно годами?

Представьте, что вы строите библиотеку. Вы можете свалить все книги в одну кучу посреди комнаты. Найти что-то будет сложно, но технически книги «хранятся». А можете расставить их по полкам, каталогизировать по жанрам и авторам. Нормализация — это и есть процесс превращения «кучи данных» в упорядоченный каталог.

Сегодня мы разберем, как организовать данные так, чтобы избежать хаоса, дублирования и ошибок. Мы доведем нашу схему CRM до Третьей нормальной формы (3NF) — золотого стандарта в проектировании реляционных баз данных.

Зачем нам вообще это нужно?

Почему нельзя просто создать одну большую таблицу Excel_Style_Table и хранить всё там? Давайте посмотрим на пример «плохой» таблицы, где мы смешали данные о клиентах и их менеджерах.

| ID Клиента | Клиент | Телефон | Менеджер | Email Менеджера | Офис Менеджера | | :--- | :--- | :--- | :--- | :--- | :--- | | 1 | ООО «Рога» | 555-01 | Петров | petrov@crm.ru | Москва | | 2 | ЗАО «Копыта» | 555-02 | Петров | petrov@crm.ru | Москва | | 3 | ИП Сидоров | 555-03 | Иванов | ivanov@crm.ru | СПб |

На первый взгляд, таблица удобная. Но при эксплуатации возникают три классические проблемы, которые называют аномалиями:

Аномалия обновления (Update Anomaly): Если менеджер Петров переедет в другой офис или сменит фамилию, нам придется искать все строки с его именем (клиенты 1 и 2) и менять данные везде. Если мы пропустим хоть одну строку, данные станут противоречивыми.

Аномалия вставки (Insert Anomaly): Мы не можем добавить в базу нового менеджера Сидорову, пока у неё нет ни одного клиента. Ведь поле ID Клиента — это первичный ключ, и оно не может быть пустым.

Аномалия удаления (Delete Anomaly): Если мы удалим единственного клиента менеджера Иванова (ИП Сидоров), мы навсегда потеряем информацию о самом Иванове (его email и офис).

Нормализация решает эти проблемы, разбивая данные на логические части.

!Визуализация процесса нормализации: переход от хаоса к структуре

Первая нормальная форма (1NF): Атомарность

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

Требования 1NF:

Все атрибуты должны быть атомарными (неделимыми).

В таблице должен быть первичный ключ.

Не должно быть повторяющихся групп атрибутов.

Пример нарушения: Представьте, что в таблице Clients в поле phone мы записали: «8-900-111-22-33, 8-495-123-45-67».

Это плохо, потому что: * Нельзя легко найти клиента по второму номеру. * Нельзя понять, сколько всего номеров у клиента.

Как исправить: Либо мы создаем отдельную таблицу ClientPhones (лучший вариант), либо гарантируем, что в поле phone хранится только один основной номер.

В нашей схеме из прошлого урока мы определили поле phone как VARCHAR(20). Если мы договоримся писать туда только один номер — мы в 1NF.

Вторая нормальная форма (2NF): Зависимость от всего ключа

Чтобы перейти ко второй форме, таблица должна быть уже в 1NF. Правило 2NF гласит: все неключевые атрибуты должны зависеть от полного первичного ключа.

Это правило актуально в основном для таблиц, где первичный ключ составной (состоит из двух и более полей). Если у вас везде id (суррогатный ключ), как у нас (client_id, contract_id), вы почти автоматически соблюдаете 2NF.

Пример нарушения (теоретический): Допустим, у нас есть таблица OrderItems (Позиции заказа) с ключом (order_id, product_id).

| order_id | product_id | quantity | product_name | | :--- | :--- | :--- | :--- | | 10 | 55 | 2 | Ноутбук |

Здесь quantity (количество) зависит от комбинации заказа и товара (сколько именно этих товаров в этом заказе). А вот product_name (название товара) зависит только от product_id. Название ноутбука не меняется от того, в каком заказе он находится.

Как исправить: Вынести product_name в отдельную таблицу Products.

Третья нормальная форма (3NF): Никаких транзитивных зависимостей

Это самый важный этап для нашей CRM. Таблица находится в 3NF, если она в 2NF и все неключевые атрибуты зависят ТОЛЬКО от первичного ключа.

Вспомните фразу: «Данные должны зависеть от ключа, от всего ключа и ни от чего, кроме ключа».

Если атрибут зависит от атрибута , а атрибут зависит от ключа (цепочка ), то это транзитивная зависимость. Её нужно устранить.

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

Анализ нашей CRM на соответствие 3NF

Вернемся к таблице Clients, которую мы создали в прошлом уроке:

Здесь всё чисто. Имя, email и телефон зависят напрямую от client_id.

Но давайте усложним задачу. Допустим, мы хотим знать, какой менеджер работает с клиентом. В плохом варианте мы добавим поля manager_name и manager_email прямо в таблицу Clients.

Здесь manager_email зависит от manager_name (или ID менеджера), который, в свою очередь, привязан к клиенту. Это транзитивная зависимость. Мы дублируем данные менеджера в каждой строке клиента.

Решение: Выделение сущности «Сотрудник»

Чтобы привести схему к 3NF, нам нужно вынести данные о менеджерах в отдельную таблицу. Назовем её Employees (Сотрудники).

#### Шаг 1. Создаем таблицу сотрудников

#### Шаг 2. Обновляем таблицу клиентов

Теперь в таблице Clients мы оставляем только ссылку на сотрудника — внешний ключ (Foreign Key).

Теперь, если менеджер сменит фамилию, мы обновим её только в одной строке таблицы Employees. Все клиенты автоматически будут ссылаться на актуальные данные. Аномалия обновления устранена!

Проверка остальных таблиц

Давайте быстро проверим наши таблицы Contracts, Payments и Tasks из прошлого урока.

Contracts (Договоры):

* Поля: contract_id, client_id, contract_number, amount, sign_date. * Зависимости: Номер, сумма и дата зависят от конкретного договора (contract_id). Клиент (client_id) — это ссылка. Транзитивных зависимостей нет. 3NF соблюдена.

Payments (Оплаты):

* Поля: payment_id, contract_id, amount, payment_date. * Зависимости: Сумма и дата относятся к конкретному платежу. 3NF соблюдена.

Tasks (Задачи):

* Поля: task_id, client_id, description, due_date, is_completed. * Зависимости: Описание и статус зависят от задачи. 3NF соблюдена.

Когда стоит остановиться?

Существуют и более строгие формы: форма Бойса-Кодда (BCNF), 4NF, 5NF. Но в реальной разработке бизнес-приложений (особенно CRM) 3NF является достаточным уровнем.

Иногда разработчики намеренно нарушают правила (это называется денормализация) ради скорости чтения данных. Например, мы могли бы хранить client_name в таблице Contracts, чтобы не делать лишнее соединение таблиц (JOIN) при каждом запросе списка договоров. Но это продвинутая техника оптимизации, и применять её нужно только тогда, когда вы точно знаете, зачем это делаете.

Для нашего курса и старта проекта строгая 3NF — лучший выбор. Она гарантирует чистоту данных.

Итоговая схема базы данных

С учетом добавления таблицы сотрудников, наша схема теперь выглядит так:

Employees (Справочник сотрудников)

Clients (Ссылается на Employees)

Contracts (Ссылается на Clients)

Payments (Ссылается на Contracts)

Tasks (Ссылается на Clients)

Мы создали гибкую структуру. Если завтра у нас появится отдел маркетинга, мы просто добавим их в таблицу Employees, и они тоже смогут работать с системой, не ломая существующую логику.

В следующей статье мы погрузимся в мир типов данных и ограничений (Constraints). Мы узнаем, почему хранить деньги в FLOAT — плохая идея, как запретить ввод отрицательных сумм и как сделать так, чтобы email всегда был уникальным.

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

Добро пожаловать на третью часть курса «Проектирование базы данных CRM: от идеи до SQL-скриптов». В предыдущих статьях мы прошли путь от абстрактных идей до нормализованной схемы, разделив данные на таблицы: Сотрудники, Клиенты, Договоры, Оплаты и Задачи. Мы создали логическую структуру, но для реальной базы данных этого недостаточно.

Сегодня мы переходим к физическому проектированию. Если логическая модель — это архитектурный чертеж дома, то физическая модель — это выбор материалов: из чего будут стены (кирпич или дерево?), какие будут замки на дверях и как проложить проводку. В мире баз данных это означает выбор типов данных, настройку ограничений (Constraints) и правил поведения связей.

Выбор типов данных: фундамент надежности

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

1. Числа и Деньги

В нашей CRM есть суммы договоров и оплат. Новички часто используют тип FLOAT или DOUBLE. Это фатальная ошибка для финансовых систем.

> Никогда не используйте типы с плавающей точкой (FLOAT, REAL) для хранения денег. Особенности двоичной арифметики приведут к ошибкам округления: 100.00 - 99.99 может внезапно стать 0.00999999.

Правильный выбор: * DECIMAL(M, D) (или NUMERIC). Это тип для хранения точных чисел. * Пример: DECIMAL(10, 2) означает, что всего мы храним 10 цифр, из которых 2 — после запятой. Максимальное число: 99 999 999.99.

Для идентификаторов (ID) мы используем целые числа: * INT (Integer): Хранит значения до 2 миллиардов. Достаточно для большинства справочников. * BIGINT: Если вы планируете хранить триллионы записей (например, логи событий), лучше брать его. * SERIAL (в PostgreSQL): Псевдотип, который автоматически создает последовательность (1, 2, 3...) для автоинкремента.

2. Строковые данные

* VARCHAR(n): Строка переменной длины с ограничением n. Идеально для имен, email, телефонов. Если строка короче n, база данных не тратит лишнее место. * TEXT: Строка неограниченной длины. Подходит для описания задач или комментариев. * CHAR(n): Строка фиксированной длины. Используется редко, например, для кодов валют (USD, EUR), где длина всегда известна.

3. Дата и время

* DATE: Только дата (2023-10-05). Подходит для даты рождения или даты подписания договора. * TIMESTAMP: Дата и время (2023-10-05 14:30:00). Нужно для фиксации момента создания записи.

Ограничения (Constraints): правила игры

База данных должна не просто хранить данные, но и защищать их от некорректного ввода. Для этого используются ограничения.

!Визуализация работы ограничений (Constraints) при вставке данных

Основные виды ограничений:

PRIMARY KEY (Первичный ключ): Уникальный идентификатор строки. Гарантирует, что запись уникальна и не пуста.

NOT NULL: Запрещает оставлять поле пустым. У клиента обязательно должно быть имя.

UNIQUE: Гарантирует уникальность значений в столбце. Например, в системе не может быть двух сотрудников с одинаковым email.

CHECK: Проверка условия. Например, сумма договора не может быть отрицательной.

DEFAULT: Значение по умолчанию. Если мы не указали дату создания задачи, пусть подставится текущее время.

Настройка связей (Foreign Keys) и каскадные действия

Мы уже знаем, что таблицы связываются через внешние ключи (FOREIGN KEY). Но что должно произойти с договорами клиента, если мы решим удалить самого клиента из базы?

Здесь мы настраиваем поведение ON DELETE и ON UPDATE:

RESTRICT (по умолчанию): Запрещает удаление. База скажет: «Нельзя удалить клиента, пока у него есть активные договоры». Это самый безопасный вариант для CRM.

CASCADE: Удаляет связанные записи. Если удалить клиента, автоматически удалятся все его договоры, оплаты и задачи. Опасно для финансовых данных, но удобно для черновых записей.

SET NULL: При удалении клиента, в поле client_id у договоров проставится NULL. Договоры станут «ничейными».

Для нашей CRM мы выберем стратегию безопасности. Мы не хотим случайно удалить историю продаж.

Итоговые SQL-скрипты (Физическая модель)

Теперь соберем все знания и напишем финальные скрипты создания таблиц (DDL), добавив типы, ограничения и правильные связи.

1. Таблица Сотрудников (Employees)

2. Таблица Клиентов (Clients)

3. Таблица Договоров (Contracts)

Добавляем проверку, что сумма положительная.

4. Таблица Оплат (Payments)

5. Таблица Задач (Tasks)

Резюме

Мы превратили нашу логическую схему в строгий набор инструкций для СУБД. Что мы сделали:

Защитили деньги: Использовали DECIMAL и CHECK (amount > 0).

Защитили уникальность: Email сотрудников и номера договоров не могут дублироваться.

Настроили связи: Мы выбрали RESTRICT для клиентов, чтобы случайно не потерять важные данные, но CASCADE для задач, чтобы не хранить мусор.

Теперь наша база данных готова к приему информации. В следующей статье мы займемся самым интересным — наполнением базы тестовыми данными и написанием первых аналитических SQL-запросов.

Реализация на SQL: написание DDL-скриптов для создания структуры базы данных

Рад приветствовать вас на четвертом этапе нашего курса «Проектирование базы данных CRM: от идеи до SQL-скриптов». Мы прошли долгий путь: от абстрактного понимания того, кто такой «Клиент», до строгой физической модели с типами данных и ограничениями. Теперь у нас есть чертеж. Пришло время взять в руки инструменты и построить фундамент.

В этой статье мы переведем наши схемы в реальный код. Мы будем писать скрипты на языке DDL (Data Definition Language). Это подмножество SQL, отвечающее за создание и изменение структуры базы данных.

Подготовка: Чистый лист

Прежде чем создавать таблицы, нужно убедиться, что мы работаем в чистой среде. В процессе разработки вы будете часто пересоздавать базу данных, исправляя ошибки проектирования. Поэтому хороший скрипт создания структуры часто начинается с команд удаления старых таблиц.

Однако, удалять таблицы нужно с умом. Если вы попытаетесь удалить таблицу Clients, на которую ссылаются Contracts, база данных выдаст ошибку. Помните наши связи? Внешние ключи держат структуру крепко.

Чтобы удалить таблицу вместе со всеми зависимостями, используется ключевое слово CASCADE.

> Важно: Никогда не запускайте команды с DROP ... CASCADE на рабочей (production) базе данных без предварительного бэкапа. Это безвозвратно уничтожит данные.

Порядок создания таблиц

В реляционных базах данных порядок имеет критическое значение. Мы не можем построить крышу, пока не возведены стены. Точно так же мы не можем создать таблицу Contracts, пока не существует таблицы Clients, на которую она ссылается.

Правило простое: сначала создаются независимые справочники, затем зависимые сущности.

!Иерархия зависимостей: от родительских таблиц к дочерним

Исходя из нашей схемы, порядок будет следующим:

Employees (ни от кого не зависит)

Clients (зависит от Employees)

Contracts и Tasks (зависят от Clients)

Payments (зависит от Contracts)

Написание DDL-скриптов

Давайте напишем код для каждой таблицы, собирая воедино все знания из предыдущих уроков о типах данных и нормализации.

1. Таблица сотрудников (Employees)

Это наш корневой справочник. Здесь мы используем ограничение UNIQUE для email, чтобы предотвратить дублирование учетных записей.

2. Таблица клиентов (Clients)

Клиенты привязаны к сотрудникам. Обратите внимание на настройку внешнего ключа (FOREIGN KEY). Мы используем ON DELETE SET NULL. Это означает, что если менеджер уволится (мы удалим его из базы), клиент не исчезнет, а просто поле manager_id станет пустым. Это безопасно для бизнеса.

Совет: Хорошей практикой считается давать имена ограничениям (CONSTRAINT), например fk_client_manager. Это поможет вам понять причину ошибки, если база данных вдруг начнет ругаться на нарушение целостности.

3. Таблица договоров (Contracts)

Здесь мы применяем тип DECIMAL(10, 2) для денег и проверку CHECK, чтобы сумма не была отрицательной. Для связи с клиентом используем ON DELETE RESTRICT — мы запрещаем удалять клиента, если у него есть действующие договоры.

4. Таблица задач (Tasks)

Задачи — это менее критичные данные. Если мы удаляем клиента (например, он был создан по ошибке), то и задачи по нему нам не нужны. Поэтому здесь уместно использовать ON DELETE CASCADE.

5. Таблица оплат (Payments)

Последнее звено нашей цепи. Оплаты привязаны к договорам.

Оптимизация: Создание индексов

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

Первичные ключи (PRIMARY KEY) и ограничения уникальности (UNIQUE) создают индексы автоматически. Однако внешние ключи (FOREIGN KEY) в некоторых СУБД (например, PostgreSQL) автоматически не индексируются.

Если мы часто будем искать «все договоры конкретного клиента», нам нужен индекс на поле client_id в таблице Contracts.

Эволюция схемы: ALTER TABLE

В реальной жизни требования меняются. Представьте, что через неделю после запуска заказчик попросил добавить в таблицу Clients поле «Источник привлечения» (откуда клиент узнал о нас).

Нам не нужно удалять таблицу и создавать её заново. Мы используем команду ALTER TABLE.

Или, например, мы решили, что описание задачи может быть необязательным:

Итоговый результат

Теперь у нас есть полный набор инструкций для создания базы данных. Этот файл с расширением .sql (например, schema.sql) можно передать любому разработчику или администратору базы данных, и он развернет точную копию вашей структуры.

Чек-лист проверки DDL-скрипта:

Синтаксис: Команды завершаются точкой с запятой ;.

Порядок: Родительские таблицы создаются раньше дочерних.

Типы данных: Деньги в DECIMAL, строки в VARCHAR.

Ограничения: Первичные и внешние ключи настроены.

Идемпотентность: Скрипт можно запускать много раз (благодаря DROP IF EXISTS), получая предсказуемый результат.

В следующей, заключительной статье нашего цикла, мы оживим эту структуру. Мы научимся наполнять базу тестовыми данными (INSERT) и писать мощные аналитические запросы (SELECT с JOIN и агрегацией), чтобы получить отчеты по продажам.

Манипулирование данными: SQL-запросы для вставки, обновления и выборки информации по клиентам

Добро пожаловать на пятую часть курса «Проектирование базы данных CRM: от идеи до SQL-скриптов». В предыдущих статьях мы проделали огромную работу: проанализировали требования, нормализовали данные, выбрали типы полей и даже создали пустые таблицы с помощью DDL-скриптов. Сейчас наша база данных похожа на только что построенный, но абсолютно пустой офисный центр. Стены есть, таблички на дверях висят, но внутри — ни души.

Сегодня мы вдохнем жизнь в нашу систему. Мы научимся работать с языком DML (Data Manipulation Language). Это набор команд, которые позволяют добавлять, читать, изменять и удалять данные. Именно эти команды будут выполняться «под капотом», когда пользователь вашей CRM нажмет кнопку «Сохранить» или «Найти».

1. Наполнение базы: Команда INSERT

Первое, что нам нужно сделать — это добавить данные. Вспомним нашу структуру: у нас есть таблица сотрудников (Employees) и таблица клиентов (Clients), которая ссылается на сотрудников. Из-за этой зависимости мы не можем добавить клиента, привязанного к несуществующему менеджеру. Поэтому начнем с сотрудников.

Синтаксис вставки

Базовая команда выглядит так:

Давайте наймем нашего первого менеджера по продажам — Анну Смирнову.

Обратите внимание:

Мы не указывали employee_id. В прошлой статье мы настроили это поле как SERIAL, поэтому база данных сама присвоит ему значение (например, 1).

Мы не указывали created_at. Для этого поля мы настроили значение по умолчанию DEFAULT CURRENT_TIMESTAMP, поэтому дата подставится автоматически.

!Визуализация процесса вставки новой строки с автоматической генерацией первичного ключа

Массовая вставка

В SQL можно добавлять несколько строк одной командой. Это работает быстрее, чем много отдельных запросов. Добавим еще двух сотрудников:

Теперь, когда у нас есть сотрудники с ID 1, 2 и 3, мы можем добавить клиентов.

Здесь мы привязали «ООО Вектор» к Анне (ID 1), а «ТехноСтрой» к Ивану (ID 2).

2. Чтение данных: Команда SELECT

Данные внутри, но как их увидеть? Для этого используется команда SELECT. Это самый мощный инструмент в арсенале аналитика и разработчика.

Простая выборка

Чтобы увидеть всё содержимое таблицы клиентов:

Звездочка * означает «показать все колонки». Однако в реальных приложениях лучше перечислять конкретные поля. Это экономит трафик и память.

Фильтрация с WHERE

Представьте, что у вас миллион клиентов. Вам не нужно видеть всех, вам нужны только клиенты конкретного менеджера. Здесь на сцену выходит оператор WHERE.

Этот запрос вернет только тех клиентов, которых ведет Анна. Мы можем использовать разные операторы сравнения: * = (равно) * <> или != (не равно) * > , < (больше, меньше) * LIKE (поиск по шаблону)

Например, найдем всех клиентов, у которых почта зарегистрирована на домене .ru:

Знак % означает «любое количество любых символов». То есть мы ищем строки, которые заканчиваются на «.ru».

Сортировка с ORDER BY

Данные в базе лежат в хаотичном порядке. Чтобы упорядочить их, используем ORDER BY.

DESC (Descending) означает сортировку по убыванию (от новых к старым). ASC (Ascending) — по возрастанию.

3. Изменение данных: Команда UPDATE

Бизнес — это динамика. Клиенты меняют телефоны, менеджеры передают дела друг другу. Для изменения существующих записей используется UPDATE.

Допустим, клиент «ООО Вектор» сменил номер телефона.

Золотое правило безопасности

> Никогда не пишите UPDATE без WHERE, если не хотите изменить всю таблицу!

Если вы напишете:

...то все клиенты в вашей базе внезапно перейдут к менеджеру с ID 2. База данных не спросит: «Вы уверены?». Она просто выполнит приказ.

!Процесс точечного обновления данных с использованием условия фильтрации

4. Удаление данных: Команда DELETE

Иногда данные нужно удалять. Например, мы создали тестового клиента по ошибке.

Здесь тоже критически важен WHERE. Без него команда DELETE FROM Clients очистит таблицу полностью.

Удаление и связи (Constraints)

В предыдущих уроках мы настроили внешние ключи. Давайте посмотрим, как они защищают нас при удалении.

Предположим, у клиента «ООО Вектор» (ID 1) уже есть подписанный договор в таблице Contracts. Что произойдет, если мы попытаемся удалить этого клиента?

База данных выдаст ошибку: ERROR: update or delete on table "clients" violates foreign key constraint...

Почему? Потому что при создании таблицы Contracts мы указали правило ON DELETE RESTRICT. СУБД говорит нам: «Я не могу удалить клиента, потому что на него ссылаются документы. Сначала удалите документы или переназначьте их».

Это и есть целостность данных в действии.

Немного математики в запросах

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

Предположим, мы хотим спрогнозировать выручку по договору, если бы мы увеличили сумму на 20% (НДС). Мы можем сделать это прямо в выборке.

Формула расчета новой цены выглядит так:

где — новая цена с учетом налога, — текущая сумма договора, а — коэффициент увеличения.

В SQL это выглядит так:

Мы не меняем данные в базе, мы просто просим СУБД посчитать результат «на лету» и показать его нам.

Резюме

Сегодня мы освоили четыре всадника управления данными — CRUD:

Create (INSERT): Создание новых записей. Помните про автоинкремент и обязательные поля.

Read (SELECT): Чтение. Используйте WHERE для фильтрации и ORDER BY для сортировки.

Update (UPDATE): Обновление. Всегда проверяйте условие WHERE.

Delete (DELETE): Удаление. Помните про связи между таблицами, которые могут блокировать удаление.

Теперь наша база наполнена тестовыми данными. Но пока мы работали с каждой таблицей отдельно. В следующей, финальной статье курса, мы научимся делать настоящую магию: объединять таблицы с помощью JOIN, чтобы получать сложные отчеты, например: «Показать всех клиентов менеджера Ивана, у которых сумма договоров превышает 1 миллион рублей».

До встречи в финале!