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

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

Типы столбцов

Тип в MySQL	Для чего
`INT`, `BIGINT`	Целые числа, идентификаторы
`DECIMAL(10, 2)`	Деньги и другие точные дробные числа
`VARCHAR(255)`	Короткая строка: имя, почта, заголовок
`TEXT`	Длинный текст
`BOOLEAN`	Да или нет
`DATE`, `DATETIME`	Дата, дата со временем
`JSON`	Набор данных произвольной структуры

Тип — первая защита данных: в столбец INT не попадёт слово, а в DATE — несуществующая дата.

Первичный ключ и ограничения

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Запись	Значение
`PRIMARY KEY`	Уникальный идентификатор строки
`AUTO_INCREMENT`	База сама выдаёт следующий номер
`NOT NULL`	Значение обязательно
`UNIQUE`	Значения не повторяются
`DEFAULT`	Значение, если его не указали

Одна сущность — одна таблица

Данные об авторе не повторяют в каждой статье. Автора хранят один раз, а статья ссылается на него по идентификатору:

CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    body TEXT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

Если автор сменит имя, править придётся одну строку, а не все его статьи.

Внешний ключ

FOREIGN KEY не даёт создать статью несуществующего автора. ON DELETE CASCADE удаляет статьи вместе с автором. Посмотрим на это в действии:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('PRAGMA foreign_keys = ON');
$pdo->exec('CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)');
$pdo->exec('CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users (id) ON DELETE CASCADE,
    title TEXT NOT NULL
)');
 
$pdo->exec("INSERT INTO users (email) VALUES ('anna@example.com')");
 
try {
    $pdo->exec("INSERT INTO posts (user_id, title) VALUES (99, 'Статья')");
} catch (PDOException $e) {
    echo 'База отклонила запись: автора с номером 99 нет';
}

Ошибку ловит сама база — даже если в коде о проверке забыли.

Многие ко многим

У статьи несколько меток, у метки много статей. Такую связь хранят в третьей таблице с парами идентификаторов:

CREATE TABLE tags (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
);
 
CREATE TABLE post_tag (
    post_id INT NOT NULL,
    tag_id INT NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE,
    FOREIGN KEY (tag_id) REFERENCES tags (id) ON DELETE CASCADE
);

Составной первичный ключ не даст дважды привязать одну метку к одной статье.

Индексы

Без индекса база для поиска просматривает всю таблицу. Индекс — что-то вроде алфавитного указателя в книге:

CREATE INDEX posts_user_id_index ON posts (user_id);
CREATE INDEX posts_created_at_index ON posts (created_at);

Индексы ставят на столбцы, по которым ищут, сортируют и соединяют таблицы. Первичный ключ и UNIQUE создают индекс сами. Лишние индексы вредят: каждый замедляет добавление и изменение строк.

Частая ошибка

Деньги нельзя хранить в FLOAT и DOUBLE: эти типы приближённые, и сумма из 0.1 + 0.2 даст 0.30000000000000004. Используйте DECIMAL(10, 2) или храните целое число копеек в INT. Вторая беда — несколько значений в одном столбце, например метки через запятую: по такому полю нельзя нормально искать. Для списков заводят отдельную таблицу.

Спасибо, что читаете!