Преобразование ER в таблицы на собеседовании системного аналитика

Зачем спрашивают на собесе SA

Преобразование концептуальной ER модели в реляционную схему — базовый навык SA. На собесе: «как сделать N:M», «куда class FK при 1:1».

Сущность → таблица

Каждая сильная сущность → таблица. Атрибуты → колонки. PK → primary key.

Customer (id PK, name, email)
→
CREATE TABLE customer (
  id BIGSERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE NOT NULL
);

Композитный атрибут (например, address = {street, city}) — разворачивается в колонки или отдельная таблица.

Multi-valued атрибут (telephones — список) — отдельная таблица с FK.

Связь 1:1

Два варианта:

Вариант A. Объединить в одну таблицу (если всегда оба обязательны).

Вариант B. Отдельная таблица + FK с UNIQUE.

Person 1 ── 1 Passport

CREATE TABLE person (id PK, name);
CREATE TABLE passport (id PK, person_id UNIQUE FK, number);

Куда FK? Часто на стороне «менее значимой» сущности. Иногда — стороне с более «обязательным» участием.

Связь 1:N

FK на стороне «многих».

Customer 1 ── * Order

CREATE TABLE customer (id PK, name);
CREATE TABLE "order" (
  id PK,
  customer_id FK REFERENCES customer(id),
  amount,
  ...
);

Optional. customer_id может быть NULL → допустим 0..*.

Mandatory. customer_id NOT NULL → 1..*.

Связь N:M

Через associative table (junction / bridge table).

Student * ── * Course

CREATE TABLE student (id PK, name);
CREATE TABLE course (id PK, name);
CREATE TABLE student_course (
  student_id FK,
  course_id FK,
  enrolled_at TIMESTAMP,
  grade NUMERIC,
  PRIMARY KEY (student_id, course_id)
);

Атрибуты на связи (grade, enrolled_at) — в associative таблице.

Если связь имеет много атрибутов — это «association entity», возможно стоит дать ему имя (Enrollment).

Рекурсивные связи

Связь сущности с самой собой.

1:N. Иерархия сотрудников.

CREATE TABLE employee (
  id PK,
  name,
  manager_id FK REFERENCES employee(id)  -- NULL для CEO
);

N:M. Связи между пользователями (друзья).

CREATE TABLE friendship (
  user_a_id FK,
  user_b_id FK,
  since DATE,
  PRIMARY KEY (user_a_id, user_b_id)
);

Симметрия. Обычно хранят (a, b) с условием a < b, чтобы не дублировать.

Наследование

ER может иметь generalization. Три подхода в реляционной модели:

1. Single table inheritance. Все в одной таблице, discriminator column.

CREATE TABLE person (
  id PK,
  type ENUM('customer', 'employee'),
  name,
  -- customer-specific
  loyalty_points NULL,
  -- employee-specific
  salary NULL
);

Простой, но много NULL.

2. Class table inheritance (joined). Таблица для каждого класса.

CREATE TABLE person (id PK, name);
CREATE TABLE customer (id PK FK person, loyalty_points);
CREATE TABLE employee (id PK FK person, salary);

Чище, но требует JOIN для полных данных.

3. Concrete table inheritance. Только подклассы — отдельные таблицы, родителя нет.

CREATE TABLE customer (id PK, name, loyalty_points);
CREATE TABLE employee (id PK, name, salary);

Дублирует общие атрибуты. Плохо для UNION queries.

Выбор: обычно single table — для простых случаев, joined — для нормальных, concrete — редко.

Частые ошибки

N:M без связующей таблицы. Невозможно представить в реляционной модели иначе.

FK не там. В 1:N — FK на стороне «многих», не «одного».

Игнорировать ON DELETE / ON UPDATE. CASCADE / RESTRICT / SET NULL — критичны для бизнес-логики.

Naming. student_course или enrollment? Если связь имеет смысл — давай имя.

Surrogate vs natural key. Использовать surrogate (BIGSERIAL / UUID) почти всегда. Natural keys — fragile.

Игнорировать UNIQUE constraints. Email пользователя должен быть UNIQUE — это не PK, но constraint.

Связанные темы

• ERD и связи на собесе SA
• Нормализация БД на собесе SA
• UML Class на собесе SA
• SQL vs NoSQL для SA
• Подготовка к собесу системного аналитика

FAQ

Слабая сущность — что это?

Сущность, которая не существует без родителя. Например, Order_Item существует только внутри Order. PK — composite (order_id + line_no).

UUID или BIGSERIAL для PK?

UUID — гибче (генерируется на клиенте, без round-trip). BIGSERIAL — компактнее (8 байт vs 16) и быстрее на индексах. UUID v7 / ULID — компромисс с timestamp prefix.

Это официальная информация?

Нет. Статья основана на классических работах (Codd, Date) и стандартных практиках database design.

Тренируйте системный анализ — откройте тренажёр с 1500+ вопросами для собесов.