Из чего состоит реляционная БД?

Из чего состоит реляционная база данных

Реляционная база данных (RDBMS) — это организованная система хранения данных, построенная на принципе отношений (relations) между сущностями. Это наиболее распространённый тип баз данных.

1. Таблицы (Tables / Relations)

Определение: Таблица — это основная структурная единица реляционной БД. Это набор строк с одинаковой структурой.

Компоненты таблицы:

Таблица: Orders

┌─────────┬──────────────┬──────────────┬─────────┐
│ order_id│ customer_id  │ amount       │ status  │  <- Атрибуты (колонки)
├─────────┼──────────────┼──────────────┼─────────┤
│ 1       │ cust-001     │ 1500.00      │ pending │  <- Кортежи (строки)
│ 2       │ cust-002     │ 2000.00      │ shipped │
│ 3       │ cust-001     │ 3500.00      │ pending │
└─────────┴──────────────┴──────────────┴─────────┘
     ▲           ▲             ▲            ▲
  Primary Key  Foreign Key   Column      Column
   (Unique)   (Links table)  (Атрибут)   (Атрибут)

Примеры таблиц в системе:

-- Таблица пользователей
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Таблица заказов
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,  -- Foreign Key
  amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) DEFAULT 'created',
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
);

2. Колонки (Columns / Attributes)

Определение: Колонка — это один атрибут, характеристика или свойство, которое описывается для каждой строки.

Компоненты колонки:

Колонка: amount
┌──────────────────┐
│ Name: amount     │  <- Название
│ Type: DECIMAL    │  <- Тип данных
│ Size: (10, 2)    │  <- Размер
│ NOT NULL         │  <- Ограничение
│ Default: 0       │  <- Значение по умолчанию
└──────────────────┘

Основные типы данных:

3. Строки (Rows / Tuples)

Определение: Строка — это одна запись в таблице, содержащая значения для каждой колонки.

Пример:

Таблица: orders

Строка 1:  order_id=1, user_id=cust-001, amount=1500, status='pending'
Строка 2:  order_id=2, user_id=cust-002, amount=2000, status='shipped'
Строка 3:  order_id=3, user_id=cust-001, amount=3500, status='pending'

4. Первичный ключ (Primary Key)

Определение: Первичный ключ — это уникальный идентификатор для каждой строки. Гарантирует уникальность и позволяет быстро найти строку.

Требования:

• Уникален: два разных значения не могут быть
• Not NULL: обязателен для каждой строки
• Immutable: не должен меняться (в идеале)
• Simple or Composite: может быть одна колонка или несколько

Примеры:

-- Simple Primary Key (одна колонка)
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  name VARCHAR(100)
);

-- Composite Primary Key (несколько колонок)
CREATE TABLE order_items (
  order_id UUID,
  product_id UUID,
  quantity INT,
  PRIMARY KEY (order_id, product_id)  -- комбинация уникальна
);

Почему UUID вместо AUTO_INCREMENT:

-- ❌ AUTO_INCREMENT: проблемы с distributed systems
CREATE TABLE orders (
  order_id SERIAL PRIMARY KEY,  -- 1, 2, 3, ... (зависит от порядка)
  ...
);

-- ✅ UUID: distributed friendly
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,  -- f47ac10b-58cc-4372-a567-0e02b2c3d479
  ...
);

5. Внешний ключ (Foreign Key)

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

Пример:

Таблица users:
┌────────┬───────┐
│user_id │ name  │
├────────┼───────┤
│ u1     │ Ivan  │
│ u2     │ Masha │
└────────┴───────┘

Таблица orders:
┌──────────┬──────────┬────────┐
│order_id  │user_id(FK)│amount │
├──────────┼──────────┼────────┤
│ o1       │ u1       │1000   │  <- ссылается на u1 в таблице users
│ o2       │ u2       │2000   │  <- ссылается на u2 в таблице users
│ o3       │ u1       │1500   │  <- ссылается на u1 в таблице users
└──────────┴──────────┴────────┘

SQL определение:

CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  amount DECIMAL(10, 2),
  FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE      -- если удалить user, удалить его заказы
    ON UPDATE CASCADE      -- если обновить user_id, обновить заказы
);

6. Ограничения (Constraints)

Определение: Ограничения — это правила целостности данных, которые гарантируют корректность информации в БД.

Основные типы:

-- PRIMARY KEY: уникален и не NULL
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  ...
);

-- UNIQUE: уникален (но может быть NULL)
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE,  -- два пользователя не могут иметь один email
  ...
);

-- NOT NULL: обязателен
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  name VARCHAR(100) NOT NULL,  -- каждый user должен иметь имя
  ...
);

-- CHECK: логическое условие
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  amount DECIMAL(10, 2) CHECK (amount > 0),  -- сумма всегда положительна
  ...
);

-- DEFAULT: значение по умолчанию
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'created',  -- если не указан, будет 'created'
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),  -- текущее время
  ...
);

-- FOREIGN KEY: ссылка на другую таблицу
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(user_id),  -- должен существовать в таблице users
  ...
);

7. Индексы (Indexes)

Определение: Индекс — это структура данных, которая ускоряет поиск информации в таблице. Работает как указатель в книге.

Типы индексов:

-- B-Tree Index (самый распространённый)
CREATE INDEX idx_orders_user_id ON orders(user_id);  -- поиск по user_id быстрее

-- Composite Index (по нескольким колонкам)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);  -- WHERE user_id=? AND status=?

-- Unique Index (гарантирует уникальность)
CREATE UNIQUE INDEX idx_users_email ON users(email);  -- email уникален

-- Partial Index (только для определённых строк)
CREATE INDEX idx_orders_active ON orders(user_id) WHERE status != 'completed';  -- только активные

-- Full-Text Index (для полнотекстового поиска)
CREATE INDEX idx_articles_content ON articles USING GIN (to_tsvector('russian', content));

Плюсы и минусы:

8. Представления (Views)

Определение: Представление — это виртуальная таблица, созданная на основе запроса к одной или нескольким реальным таблицам.

Пример:

-- Создаём view: активные заказы
CREATE VIEW active_orders AS
SELECT 
  o.order_id,
  o.user_id,
  u.name as customer_name,
  o.amount,
  o.created_at
FROM orders o
JOIN users u ON o.user_id = u.user_id
WHERE o.status IN ('created', 'confirmed', 'shipped');

-- Использование view (как обычную таблицу)
SELECT * FROM active_orders WHERE customer_name = 'Ivan';

Плюсы:

• Упрощает complex запросы
• Скрывает деталь реализации
• Обеспечивает security (ограничиваем доступ к определённым колонкам)
• Переиспользуемость

9. Схема (Schema)

Определение: Схема — это логическое группирование таблиц, индексов, представлений в одну namespace.

Пример:

-- Создаём схему для приложения логистики
CREATE SCHEMA logistics;

-- Создаём таблицы в этой схеме
CREATE TABLE logistics.orders (...);
CREATE TABLE logistics.deliveries (...);
CREATE TABLE logistics.drivers (...);

-- Используем таблицы
SELECT * FROM logistics.orders;

-- Или если схема по умолчанию
SET search_path TO logistics;
SELECT * FROM orders;  -- работает

10. Транзакции (Transactions)

Определение: Транзакция — это группа операций, которые выполняются как единое целое (all or nothing).

Пример:

BEGIN;  -- начало транзакции

-- Операция 1: вычесть деньги с аккаунта A
UPDATE accounts SET balance = balance - 100 WHERE account_id = 'A';

-- Операция 2: добавить деньги на аккаунт B
UPDATE accounts SET balance = balance + 100 WHERE account_id = 'B';

-- Если всё успешно
COMMIT;  -- сохраняем обе операции

-- Если произошла ошибка
ROLLBACK;  -- отменяем обе операции (как будто ничего не было)

ACID свойства:

• A (Atomicity): всё или ничего
• C (Consistency): данные остаются консистентны
• I (Isolation): одна транзакция не видит другую до завершения
• D (Durability): после COMMIT, данные сохранены

11. Процедуры и функции (Stored Procedures & Functions)

Определение: Реиспользуемый код, сохранённый в БД, который выполняется на сервере БД.

Пример (PostgreSQL PL/pgSQL):

-- Функция: рассчитать скидку
CREATE FUNCTION calculate_discount(amount DECIMAL, is_vip BOOLEAN)
RETURNS DECIMAL AS $$
BEGIN
  IF is_vip AND amount >= 1000 THEN
    RETURN amount * 0.9;  -- 10% скидка для VIP
  ELSE
    RETURN amount;
  END IF;
END;
$$ LANGUAGE plpgsql;

-- Использование
SELECT calculate_discount(1500, true);  -- возвращает 1350

Плюсы:

• Выполняется на сервере (нет сетевого overhead)
• Переиспользуемый код
• Безопасность (бизнес-логика на БД)

12. Триггеры (Triggers)

Определение: Автоматическое действие, которое выполняется при определённом событии (INSERT, UPDATE, DELETE).

Пример:

-- Триггер: автоматически обновить updated_at при изменении
CREATE TRIGGER update_orders_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
SET NEW.updated_at = NOW();

-- Триггер: логировать изменения в audit таблицу
CREATE TRIGGER audit_order_changes
AFTER UPDATE ON orders
FOR EACH ROW
INSERT INTO order_audit (order_id, old_status, new_status, changed_at)
VALUES (NEW.order_id, OLD.status, NEW.status, NOW());

13. Типы данных специального назначения

JSON/JSONB (PostgreSQL):

CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  metadata JSONB,  -- гибкие данные
  ...
);

-- Вставка
INSERT INTO orders VALUES (
  'o1',
  '{"shipping_method": "express", "gift_wrap": true}'
);

-- Запрос
SELECT * FROM orders WHERE metadata->>'shipping_method' = 'express';

14. Инструмент управления версий схемы (Migrations)

Определение: Миграции — это версионированные скрипты, которые изменяют схему БД.

Пример (Goose):

-- migration 001_initial_schema.sql
CREATE TABLE users (
  user_id UUID PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- migration 002_add_orders_table.sql
CREATE TABLE orders (
  order_id UUID PRIMARY KEY,
  user_id UUID NOT NULL REFERENCES users(user_id),
  amount DECIMAL(10, 2),
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

Архитектура реляционной БД в целом

┌──────────────────────────────────────────────┐
│       Query Interface (SQL)                  │
└─────────────────────┬────────────────────────┘
                      │
┌─────────────────────▼────────────────────────┐
│    Query Optimizer & Parser                 │
│    (Оптимизирует как выполнить запрос)     │
└─────────────────────┬────────────────────────┘
                      │
┌─────────────────────▼────────────────────────┐
│    Storage Engine (Innodb, WAL)             │
│    (Читает/пишет в диск)                   │
└─────────────────────┬────────────────────────┘
                      │
        ┌─────────────┴──────────────┐
        │                            │
   ┌────▼──────┐          ┌──────────▼────┐
   │   Files   │          │   Transaction │
   │   (*.ibd) │          │   Log (WAL)   │
   └───────────┘          └───────────────┘

Как это работает в моём проекте (PrepBro)

В системе, где я работаю:

Таблицы:
- users (пользователи-агенты)
- questions (вопросы для интервью)
- professions (профессии)
- answers (ответы на вопросы)
- sessions (сессии интервью)

Отношения:
users (1) → (N) sessions
sessions (1) → (N) answers
questions (1) → (N) answers
professions (1) → (N) questions

Индексы:
- PRIMARY KEY на все таблицы (быстрый поиск)
- FOREIGN KEY для целостности
- Индекс на (profession_id, question_id) для быстрого получения следующего вопроса

Транзакции:
- При submit answer: сохранить + опубликовать событие (atomic)
- При scoring: обновить ответ + обновить скор (atomic)

Этот набор компонентов обеспечивает надежное, быстрое и безопасное хранилище данных.