Проектирование баз данных: нормализация, типы данных, первичные и внешние ключи
В предыдущих статьях мы научились извлекать данные с помощью SELECT, фильтровать их, соединять таблицы через JOIN и даже группировать результаты. Но мы работали с уже готовыми таблицами, которые кто-то заботливо создал до нас. В реальной жизни, прежде чем писать запросы, нужно спроектировать структуру хранения данных.
Представьте, что вы строите дом. SELECT и JOIN — это отделка, покраска стен и расстановка мебели. Проектирование базы данных (БД) — это заливка фундамента и возведение несущих стен. Если ошибиться на этом этапе, дом может рухнуть под собственным весом, как только вы добавите второй этаж.
В этой статье мы разберем, как превратить хаотичный набор данных в стройную, эффективную и надежную структуру. Мы поговорим о типах данных, ключах и «Священном Граале» проектировщиков — нормализации.
Типы данных: Фундамент каждой колонки
Когда вы создаете таблицу в Excel, вы можете писать в ячейку что угодно: сегодня число, завтра текст, послезавтра дату. Базы данных такого не прощают. Для каждого столбца мы должны строго определить тип данных. Это позволяет СУБД (Системе Управления Базами Данных) оптимизировать хранение и предотвращать ошибки.
Рассмотрим основные категории типов данных, которые используются в 99% случаев.
1. Числовые типы (Numeric)
Используются для хранения количественных значений, над которыми можно производить математические операции.
* INTEGER (INT): Целые числа. Например, количество книг на складе, ID пользователя, год издания.
* DECIMAL / NUMERIC: Числа с фиксированной точностью. Идеально подходят для денег. Например, цена книги 599.99. В отличие от типов с плавающей точкой (FLOAT), здесь нет ошибок округления.
* FLOAT / DOUBLE: Числа с плавающей точкой. Используются для научных вычислений, где важен масштаб, а не идеальная точность до копейки (например, координаты GPS или вес в граммах).
2. Строковые типы (String)
* CHAR(n): Строка фиксированной длины. Если вы задали CHAR(10), а записали слово «SQL», база данных добьет остаток пробелами. Хорошо подходит для кодов стран (RU, US) или автомобильных номеров.
* VARCHAR(n): Строка переменной длины. Самый популярный тип. VARCHAR(255) означает, что можно записать до 255 символов, но если вы запишете 3 символа, место будет занято только под 3 символа (плюс байт длины). Используется для имен, адресов, названий книг.
* TEXT: Для очень длинных текстов, например, описания книги или содержания статьи блога.
3. Дата и время (Date & Time)
* DATE: Только дата (2023-10-05). День рождения, дата публикации.
* TIME: Только время (14:30:00). Время начала урока.
* DATETIME / TIMESTAMP: И дата, и время. Момент совершения покупки.
4. Логический тип (Boolean)
* BOOLEAN: Хранит TRUE (истина) или FALSE (ложь). Например, флаг is_active (активен ли пользователь) или in_stock (есть ли товар в наличии).
> Совет: Всегда выбирайте минимально необходимый тип данных. Не используйте TEXT для номера телефона и FLOAT для цен. Это экономит место и ускоряет поиск.
Ключи: Скелет базы данных
Чтобы таблицы могли взаимодействовать друг с другом (как мы делали это с помощью JOIN), нам нужны специальные метки — ключи.
Первичный ключ (Primary Key)
Первичный ключ (PK) — это столбец (или набор столбцов), который уникально идентифицирует каждую строку в таблице.
Представьте паспортный стол. У многих людей могут совпадать Имя и Фамилия. Даже дата рождения может совпасть. Но номер паспорта уникален. В базе данных роль номера паспорта играет Primary Key.
Правила первичного ключа:
Уникальность: Значение не может повторяться.
NOT NULL: Ключ не может быть пустым.
Неизменность: В идеале, значение первичного ключа никогда не должно меняться.Чаще всего в качестве PK используют искусственное поле id (целое число), которое автоматически увеличивается с каждой новой записью (AUTO_INCREMENT).
Внешний ключ (Foreign Key)
Внешний ключ (FK) — это столбец, который ссылается на Первичный ключ другой таблицы. Именно он создает связь между таблицами.
Если в таблице Orders (Заказы) есть поле customer_id, которое указывает на id в таблице Customers (Клиенты), то customer_id — это внешний ключ.
!Визуализация связи Первичного и Внешнего ключей.
Зачем это нужно?
Внешние ключи обеспечивают целостность данных. База данных не позволит вам:
* Добавить заказ для клиента, которого не существует в таблице Customers.
* Удалить клиента, у которого есть активные заказы (сначала нужно удалить заказы).
Нормализация: Наводим порядок
Нормализация — это процесс организации данных в базе, цель которого — устранить избыточность (дублирование) и обеспечить логическую связность.
Существует несколько «Нормальных форм» (НФ). Мы рассмотрим три основные, которых достаточно для 95% задач.
Первая нормальная форма (1НФ): Атомарность
Правило: В каждой ячейке таблицы должно храниться только одно значение. Таблица не должна содержать списков.
Пример нарушения:
Представьте таблицу Students, где в поле Phone записано: «89001112233, 89005556677».
Почему это плохо?
Как найти студента по второму номеру телефона? Как изменить один из номеров? Придется считывать строку, разбивать её запятыми, искать нужное... Это медленно и неудобно.
Решение:
Либо создать отдельную строку для каждого телефона, либо (что лучше) вынести телефоны в отдельную таблицу Phones.
Вторая нормальная форма (2НФ): Зависимость от ключа
Правило: Таблица должна находиться в 1НФ, и все неключевые столбцы должны зависеть от полного первичного ключа.
Это правило актуально, когда у вас составной первичный ключ (состоит из двух и более полей).
Пример нарушения:
Таблица OrderItems (Состав заказа). Ключ составной: OrderID + ProductID.
Поля: OrderID, ProductID, Quantity (количество), ProductName (название товара).
Здесь Quantity зависит от обоих ключей (сколько конкретного товара в конкретном заказе). А вот ProductName зависит только от ProductID. Название товара не меняется от заказа к заказу.
Почему это плохо?
Мы дублируем название товара в каждой строке заказа. Если название изменится, нам придется обновлять миллионы строк заказов.
Решение:
Вынести ProductName в отдельную таблицу Products.
Третья нормальная форма (3НФ): Нет транзитивных зависимостей
Правило: Таблица должна быть во 2НФ, и неключевые столбцы не должны зависеть от других неключевых столбцов.
Проще говоря: «Все поля должны зависеть от ключа, всего ключа и ничего кроме ключа».
Пример нарушения:
Таблица Customers. Поля: id, Name, City, ZipCode (почтовый индекс).
Здесь City зависит от ZipCode. Если мы знаем индекс, мы знаем город. Получается цепочка: id -> ZipCode -> City.
Почему это плохо?
Если в городе поменяется индексация, нам придется искать всех клиентов и менять им города. Также есть риск, что для одного индекса мы по ошибке напишем разные города.
Решение:
Создать таблицу ZipCodes (ZipCode, City) и оставить в клиентах только индекс.
> Примечание: На практике 3НФ иногда нарушают сознательно ради производительности, чтобы не делать лишний JOIN для получения названия города. Это называется денормализацией, но делать это нужно осознанно.
!Процесс разделения одной большой таблицы на несколько нормализованных.
Практический пример: Книжный магазин
Давайте применим знания и спроектируем базу для нашего магазина, избегая ошибок.
Плохой дизайн (Excel-style):
Одна таблица Sales:
OrderDate, CustomerName, CustomerEmail, BookTitle, AuthorName, Price, Quantity.
Проблемы:
Имя автора дублируется для каждой его книги.
Данные клиента дублируются для каждого заказа.
Если удалить все заказы книги, мы забудем, что такая книга вообще существовала.Хороший дизайн (Нормализованный):
Authors (Авторы)
*
id (PK, INT)
*
name (VARCHAR)
Books (Книги)
*
id (PK, INT)
*
title (VARCHAR)
*
price (DECIMAL)
*
author_id (FK, INT) -> ссылается на Authors.id
Customers (Клиенты)
*
id (PK, INT)
*
email (VARCHAR, Unique)
*
name (VARCHAR)
Orders (Заказы)
*
id (PK, INT)
*
order_date (DATETIME)
*
customer_id (FK, INT) -> ссылается на Customers.id
OrderDetails (Детали заказа — связующая таблица)
*
order_id (FK, INT)
*
book_id (FK, INT)
*
quantity (INT)
Составной PK:* (
order_id,
book_id)
Такая структура позволяет легко менять цены, переименовывать авторов и хранить историю заказов без дублирования информации.
Заключение
Проектирование базы данных — это баланс между строгими правилами нормализации и реальной производительностью.
Главные выводы:
Выбирайте правильные типы данных (числа для чисел, даты для дат).
Используйте Первичные ключи для идентификации строк.
Связывайте таблицы через Внешние ключи.
Применяйте Нормализацию, чтобы избежать хаоса и дублей.Теперь, когда у нас есть надежный фундамент, в следующей статье мы поговорим о том, как изменять структуру уже существующих таблиц и управлять данными с помощью команд INSERT, UPDATE и DELETE.