Нормализация баз данных глазами разработчика

Оглавление

  1. Введение
  2. Первая нормальная форма (1NF)
  3. Вторая нормальная форма (2NF)
  4. Третья нормальная форма (3NF)
  5. Нормальная форма Бойса-Кодда (BCNF)
  6. Четвертая нормальная форма (4NF)
  7. Пятая нормальная форма (5NF)
  8. Заключение

Введение

Нормализация базы данных – это процесс организации данных в реляционной базе с целью устранения избыточности и аномалий в данных. Проще говоря, мы структурируем таблицы и связи между ними так, чтобы каждая “факт” хранился в одном месте. Это снижает риск несогласованности данных, облегчает их обновление и экономит место. В этой статье мы разберем нормальные формы – формальные правила нормализации – от первой до пятой (1NF, 2NF, 3NF, 4NF, 5NF). Пошагово рассмотрим, как улучшать дизайн базы данных на каждом этапе, с примерами из жизни (например, база интернет-магазина). Также мы покажем примеры SQL-схем для PostgreSQL и продемонстрируем, как работать с нормализованной базой данных через Java (Spring Data JPA) и Go (pgx).

Нормальная форма vs нормализация. Здесь важно не запутаться в терминах: нормализация – это процесс приведения схемы базы к определенным правилам, а нормальные формы – сами эти правила или этапы. Например, говорят “таблица находится в третьей нормальной форме”, имея в виду, что к ней применили первые три шага нормализации. Обычно на практике достаточно довести дизайн базы до третьей нормальной формы – этого уже хватает, чтобы избавиться от большинства проблем. Более высокие формы (4NF, 5NF) в реальных приложениях встречаются редко, но мы их кратко рассмотрим для полноты картины.

Почему нужна нормализация? Новички иногда проектируют базы с дублирующимися данными или “таблицами-монстрами”, куда сваливается вся информация. Это ведет к ряду проблем, называемых аномалиями:

  • Аномалия обновления. Если одно и то же значение хранится в нескольких строках, при изменении данных есть риск поправить не во всех местах, и в базе появятся противоречия. Например, магазин хранит название товара в разных таблицах; при переименовании товара легко забыть обновить одно из вхождений, и часть записей будет с устаревшим названием.
  • Аномалия вставки. Избыточный дизайн может мешать добавить новые данные. Например, если таблица “Заказы” требует указать хотя бы один товар и клиента, то мы не сможем занести в базу новый заказ, пока не знаем всех связанных данных.
  • Аномалия удаления. Удаляя запись, можно нечаянно потерять связанную информацию. Классический пример: если в единой таблице “Сотрудники и Отделы” уволить последнего сотрудника отдела, исчезнет и сама запись об отделе (будто отдела не существует).

Нормализация решает эти проблемы за счет декомпозиции – разделения больших таблиц на более мелкие, связанные между собой ключами. Цель – каждый факт хранится в одном месте, и данные зависят от ключей, а не от других неявных условий. Далее мы перейдем к нормальным формам и шагам нормализации, каждый раз разбирая определение и пример.

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

Рис. 1: пример ненормализованной таблицы заказа.

В этой единственной таблице хранятся данные о заказе интернет-магазина: есть поля для покупателя (имя, город) и несколько полей для товаров (product1, product2, product3). Такой дизайн не соответствует даже первой нормальной форме: во-первых, нет четкого ограничения на количество товаров (добавлены повторяющиеся столбцы Product1, Product2 и т.д.), во-вторых, поля с товарами не атомарны – каждый из них может содержать комплексное значение (например, сразу название и количество товара).

Определение 1NF: таблица находится в первой нормальной форме, если каждое поле содержит неделимое, атомарное значение, и в таблице нет повторяющихся групп столбцов. Кроме того, у таблицы должен быть определен первичный ключ для уникальной идентификации строк. Проще говоря, 1NF требует избавиться от ситуаций, когда в одной ячейке таблицы запихан список или несколько значений (например, несколько телефонов через запятую), или когда таблица пытается хранить несколько однотипных сущностей в одной строке.

Как добиться 1NF: Все повторяющиеся или составные данные нужно вынести так, чтобы одна строка – один факт. В нашем примере заказ с тремя товарами следует представить не одной строкой с несколькими столбцами товаров, а несколькими строками – по одной на каждый товар в заказе. Обычно для этого заводят отдельную таблицу для сущности “позиция заказа”.

Рис. 2: Декомпозиция до 1NF – разделение заказа и позиций заказа.

Мы создали две связанные таблицы: Orders и OrderItems. Таблица Orders содержит сведения о самом заказе (ID заказа, дату, данные покупателя), а таблица OrderItems хранит позиции – отдельные товары, входящие в заказ, с указанием количества. Теперь каждая ячейка содержит одно значение (например, product_name хранит название одного товара, а не список), и структура не зависит от того, сколько товаров в заказе – для каждого товара просто добавляется новая строка в OrderItems. Связь между таблицами обеспечивается внешним ключом order_id в OrderItems, указывающим на Orders. Таким образом, первая нормальная форма достигнута: данные атомарны, нет повторяющихся групп столбцов.

Обратите внимание: иногда для формального достижения 1NF новички идут по неверному пути – например, заведут фиксированное число колонок под товары (product1, product2, …), как было в исходной таблице. Формально значения в таких столбцах атомарны, но такая схема негибкая: если товаров больше максимального числа столбцов, данные не добавишь. Правильный подход – именно разбить на связанные таблицы: выделить сущности (заказ, товар) и связать через новую таблицу (позиции заказа), как мы сделали.

После 1NF дизайн стал лучше, однако в новой структуре появились другие проблемы – они проявляются на уровне второй нормальной формы.

DDL (PostgreSQL)

CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
order_date DATE,
customer_name VARCHAR,
customer_city VARCHAR
);

CREATE TABLE order_items (
item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_name VARCHAR,
product_price DECIMAL,
quantity INT
);

Java (Spring Data JPA)

@Entity @Table(name = "orders")
public class Order {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long orderId;
    private LocalDate orderDate;
    private String customerName;
    private String customerCity;

    @OneToMany(cascade = CascadeType.ALL)
    @JoinColumn(name = "order_id")
    private List<OrderItem> items = new ArrayList<>();
}

@Entity @Table(name = "order_items")
public class OrderItem {
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long itemId;
    private String productName;
    private BigDecimal productPrice;
    private int quantity;
}

public interface OrderRepository extends JpaRepository<Order, Long> {
    List<Order> findByCustomerName(String customerName);
}

public interface OrderItemRepository extends JpaRepository<OrderItem, Long> {
    List<OrderItem> findByProductName(String productName);
}

@Service
public class OrderService {
    @Autowired private OrderRepository orderRepo;
    @Autowired private OrderItemRepository itemRepo;

    @Transactional
    public Order createOrder() {
        Order order = new Order();
        order.setOrderDate(LocalDate.now());
        order.setCustomerName("Иван Иванов");
        order.setCustomerCity("Москва");

        OrderItem item1 = new OrderItem();
        item1.setProductName("Телефон");
        item1.setProductPrice(new BigDecimal("25000"));
        item1.setQuantity(1);

        OrderItem item2 = new OrderItem();
        item2.setProductName("Чехол");
        item2.setProductPrice(new BigDecimal("1500"));
        item2.setQuantity(2);

        order.getItems().addAll(List.of(item1, item2));

        return orderRepo.save(order);
    }

    @Transactional(readOnly = true)
    public void printOrdersByCustomer(String customerName) {
        List<Order> orders = orderRepo.findByCustomerName(customerName);
        for (Order order : orders) {
            System.out.printf("Заказ #%d (%s), город: %s%n",
                order.getOrderId(), order.getOrderDate(), order.getCustomerCity());
            for (OrderItem item : order.getItems()) {
                System.out.printf("- Товар: %s, цена: %s, кол-во: %d%n",
                    item.getProductName(), item.getProductPrice(), item.getQuantity());
            }
        }
    }
}

Go (pgx)

package main

import (
    "context"
    "fmt"
    "log"
    "time"
    "github.com/jackc/pgx/v5"
)

type Order struct {
    OrderID      int
    OrderDate    time.Time
    CustomerName string
    CustomerCity string
    Items        []OrderItem
}

type OrderItem struct {
    ItemID       int
    OrderID      int
    ProductName  string
    ProductPrice float64
    Quantity     int
}

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
    if err != nil {
        log.Fatal(err)
    }
    defer conn.Close(ctx)

    createTables(ctx, conn)

    order := Order{
        OrderDate:    time.Now(),
        CustomerName: "Иван Иванов",
        CustomerCity: "Москва",
        Items: []OrderItem{
            {ProductName: "Телефон", ProductPrice: 25000, Quantity: 1},
            {ProductName: "Чехол", ProductPrice: 1500, Quantity: 2},
        },
    }

    saveOrder(ctx, conn, &order)
    printOrders(ctx, conn)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
    conn.Exec(ctx, `
    CREATE TABLE IF NOT EXISTS orders(
        order_id SERIAL PRIMARY KEY, order_date DATE,
        customer_name VARCHAR, customer_city VARCHAR);
    
    CREATE TABLE IF NOT EXISTS order_items(
        item_id SERIAL PRIMARY KEY, order_id INT REFERENCES orders(order_id),
        product_name VARCHAR, product_price DECIMAL, quantity INT);`)
}

func saveOrder(ctx context.Context, conn *pgx.Conn, order *Order) {
    conn.QueryRow(ctx,
        `INSERT INTO orders(order_date, customer_name, customer_city)
        VALUES($1,$2,$3) RETURNING order_id`,
        order.OrderDate, order.CustomerName, order.CustomerCity).Scan(&order.OrderID)

    for i := range order.Items {
        conn.QueryRow(ctx,
            `INSERT INTO order_items(order_id, product_name, product_price, quantity)
            VALUES($1,$2,$3,$4) RETURNING item_id`,
            order.OrderID, order.Items[i].ProductName,
            order.Items[i].ProductPrice, order.Items[i].Quantity).Scan(&order.Items[i].ItemID)
    }
}

func printOrders(ctx context.Context, conn *pgx.Conn) {
    rows, _ := conn.Query(ctx, `
    SELECT o.order_id, o.order_date, o.customer_name, o.customer_city,
           i.item_id, i.product_name, i.product_price, i.quantity
    FROM orders o JOIN order_items i ON o.order_id = i.order_id ORDER BY o.order_id`)

    defer rows.Close()
    orders := map[int]*Order{}

    for rows.Next() {
        var o Order
        var i OrderItem
        rows.Scan(&o.OrderID, &o.OrderDate, &o.CustomerName, &o.CustomerCity,
            &i.ItemID, &i.ProductName, &i.ProductPrice, &i.Quantity)

        order, exists := orders[o.OrderID]
        if !exists {
            order = &Order{o.OrderID, o.OrderDate, o.CustomerName, o.CustomerCity, []OrderItem{}}
            orders[o.OrderID] = order
        }
        order.Items = append(order.Items, i)
    }

    for _, o := range orders {
        fmt.Printf("Заказ #%d (%s), клиент: %s (%s)\n",
            o.OrderID, o.OrderDate.Format("02.01.2006"), o.CustomerName, o.CustomerCity)
        for _, i := range o.Items {
            fmt.Printf("- %s: %d шт., %.2f руб.\n",
                i.ProductName, i.Quantity, i.ProductPrice)
        }
    }
}

Вторая нормальная форма (2NF)

После приведения к 1NF мы избавились от повторяющихся групп и многозначных полей, но появились частичные зависимости. В таблице OrderItems сейчас первичный ключ составной: вероятно, комбинация (order_id, product_name) или (order_id, product_id) (последнего у нас пока нет) уникально определяет строку. Частичная (неполная) зависимость – это когда атрибут зависит только от части составного ключа, а не от всего ключа целиком. Посмотрим на наши таблицы после 1NF:

  • В OrderItems атрибуты product_name и price зависят только от товара, но не от конкретного заказа. То есть для одного и того же товара Product X цена повторится во всех строках OrderItems, где этот товар продан. Получается дублирование – признак ненормализованности.
  • В OrderItems атрибут order_id – часть ключа – влияет только на количество (quantity), а product_name/price вообще от order_id не зависят.
  • В таблице Orders пока ключом является order_id (он простой, не составной), так что проблема частичной зависимости для нее не актуальна. Однако в Orders все еще повторяется информация о покупателях, если один клиент делает несколько заказов (это не частичная, а транзитивная зависимость, о ней ниже).

Определение 2NF: Таблица находится во второй нормальной форме, если она уже в 1NF и каждый неключевой столбец полностью зависит от всего состава первичного ключа. Иными словами, нет ситуации, когда часть составного ключа определяет какое-то значение. Если первичный ключ не составной (а один столбец), то выполнению 2NF ничто не мешает автоматически.

Как добиться 2NF: Нужно выделить отдельные таблицы для данных, которые зависели лишь от части ключа. В нашем примере очевидный кандидат – информация о товарах. Название и цена товара зависят только от самого товара, и их не следует дублировать в таблице OrderItems. Решение: создать таблицу Products и хранить там product_id (как первичный ключ), name и price. А в OrderItems оставить только ссылки на товар по product_id вместо дублирования его названия и цены.

Рис. 3: Схема после приведения ко 2NF – введена таблица Products.

Мы добавили таблицу Products и заменили product_name на внешний ключ product_id в таблице OrderItems. Теперь каждый неключевой атрибут зависит от полного ключа: в OrderItems ключ составной (order_id, product_id) – количество quantity зависит от конкретной пары “этот товар в этом заказе”, и не существует атрибутов, зависящих только от order_id или только от product_id. Все данные о товаре (название, цена) хранятся единоразово в таблице Products. Мы устранили повторение цены и названия товара в каждой строке OrderItems, добившись 2NF.

Однако взглянем на таблицу Orders: она содержит, например, customer_name и customer_city. Первичный ключ Orders – order_id. Формально, раз он не составной, 2NF тут не нарушается (каждый столбец и так зависит от order_id). Но логически видно, что информация о покупателе дублируется: один и тот же клиент в разных заказах будет иметь одинаковое имя, город и т.д. Это уже не частичная, а транзитивная функциональная зависимость: атрибуты клиента зависят от заказа не напрямую, а через сущность “Клиент”. Это нарушение 3NF, к которой мы переходим.

DDL (PostgreSQL)

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_name VARCHAR,
    customer_city VARCHAR
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR,
    product_price DECIMAL
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT
);

Java (Spring Data JPA)

@Entity @Table(name = "orders")
class Order {
    @Id @GeneratedValue
    private Long orderId;
    private LocalDate orderDate;
    private String customerName;
    private String customerCity;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "order")
    private List<OrderItem> items = new ArrayList<>();
}

@Entity @Table(name = "products")
class Product {
    @Id @GeneratedValue
    private Long productId;
    private String productName;
    private BigDecimal productPrice;
}

@Entity @Table(name = "order_items")
class OrderItem {
    @Id @GeneratedValue
    private Long itemId;
    private int quantity;

    @ManyToOne
    @JoinColumn(name = "order_id")
    private Order order;

    @ManyToOne
    @JoinColumn(name = "product_id")
    private Product product;
}

interface OrderRepo extends JpaRepository<Order, Long> {}
interface ProductRepo extends JpaRepository<Product, Long> {}
interface OrderItemRepo extends JpaRepository<OrderItem, Long> {}

@Service
class OrderService {
    @Autowired private OrderRepo orderRepo;
    @Autowired private ProductRepo productRepo;

    @Transactional
    public void createOrder() {
        Product phone = new Product();
        phone.setProductName("Телефон");
        phone.setProductPrice(new BigDecimal("25000"));
        productRepo.save(phone);

        Product caseProd = new Product();
        caseProd.setProductName("Чехол");
        caseProd.setProductPrice(new BigDecimal("1500"));
        productRepo.save(caseProd);

        Order order = new Order();
        order.setOrderDate(LocalDate.now());
        order.setCustomerName("Иван Иванов");
        order.setCustomerCity("Москва");

        OrderItem item1 = new OrderItem();
        item1.setOrder(order);
        item1.setProduct(phone);
        item1.setQuantity(1);

        OrderItem item2 = new OrderItem();
        item2.setOrder(order);
        item2.setProduct(caseProd);
        item2.setQuantity(2);

        order.setItems(List.of(item1, item2));
        orderRepo.save(order);
    }
}

Go (pgx)

package main

import (
    "context"
    "fmt"
    "log"
    "time"
    "github.com/jackc/pgx/v5"
)

type Order struct {
    OrderID      int
    OrderDate    time.Time
    CustomerName string
    CustomerCity string
    Items        []OrderItem
}

type Product struct {
    ProductID    int
    ProductName  string
    ProductPrice float64
}

type OrderItem struct {
    ItemID    int
    OrderID   int
    ProductID int
    Quantity  int
    Product   Product
}

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
    if err != nil { log.Fatal(err) }
    defer conn.Close(ctx)

    createTables(ctx, conn)

    phoneID := insertProduct(ctx, conn, "Телефон", 25000)
    caseID := insertProduct(ctx, conn, "Чехол", 1500)

    order := Order{
        OrderDate:    time.Now(),
        CustomerName: "Иван Иванов",
        CustomerCity: "Москва",
        Items: []OrderItem{
            {ProductID: phoneID, Quantity: 1},
            {ProductID: caseID, Quantity: 2},
        },
    }

    saveOrder(ctx, conn, &order)
    printOrders(ctx, conn)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
    conn.Exec(ctx, `
    CREATE TABLE IF NOT EXISTS orders (
        order_id SERIAL PRIMARY KEY, order_date DATE,
        customer_name VARCHAR, customer_city VARCHAR);
    CREATE TABLE IF NOT EXISTS products (
        product_id SERIAL PRIMARY KEY, product_name VARCHAR,
        product_price DECIMAL);
    CREATE TABLE IF NOT EXISTS order_items (
        item_id SERIAL PRIMARY KEY, order_id INT REFERENCES orders,
        product_id INT REFERENCES products, quantity INT);`)
}

func insertProduct(ctx context.Context, conn *pgx.Conn, name string, price float64) int {
    var id int
    conn.QueryRow(ctx, `INSERT INTO products(product_name, product_price)
        VALUES($1,$2) RETURNING product_id`, name, price).Scan(&id)
    return id
}

func saveOrder(ctx context.Context, conn *pgx.Conn, order *Order) {
    conn.QueryRow(ctx, `INSERT INTO orders(order_date, customer_name, customer_city)
        VALUES($1,$2,$3) RETURNING order_id`,
        order.OrderDate, order.CustomerName, order.CustomerCity).Scan(&order.OrderID)

    for i := range order.Items {
        conn.QueryRow(ctx,
            `INSERT INTO order_items(order_id, product_id, quantity)
            VALUES($1,$2,$3) RETURNING item_id`,
            order.OrderID, order.Items[i].ProductID, order.Items[i].Quantity).Scan(&order.Items[i].ItemID)
    }
}

func printOrders(ctx context.Context, conn *pgx.Conn) {
    rows, _ := conn.Query(ctx, `
    SELECT o.order_id, o.order_date, o.customer_name, o.customer_city,
           i.item_id, p.product_name, p.product_price, i.quantity
    FROM orders o
    JOIN order_items i ON o.order_id = i.order_id
    JOIN products p ON i.product_id = p.product_id ORDER BY o.order_id`)

    defer rows.Close()
    orders := map[int]*Order{}

    for rows.Next() {
        var o Order
        var i OrderItem
        rows.Scan(&o.OrderID, &o.OrderDate, &o.CustomerName, &o.CustomerCity,
            &i.ItemID, &i.Product.ProductName, &i.Product.ProductPrice, &i.Quantity)

        order, exists := orders[o.OrderID]
        if !exists {
            order = &Order{o.OrderID, o.OrderDate, o.CustomerName, o.CustomerCity, []OrderItem{}}
            orders[o.OrderID] = order
        }
        order.Items = append(order.Items, i)
    }

    for _, o := range orders {
        fmt.Printf("Заказ #%d (%s), клиент: %s (%s)\n",
            o.OrderID, o.OrderDate.Format("02.01.2006"), o.CustomerName, o.CustomerCity)
        for _, i := range o.Items {
            fmt.Printf("- %s: %d шт., %.2f руб.\n",
                i.Product.ProductName, i.Quantity, i.Product.ProductPrice)
        }
    }
}

Третья нормальная форма (3NF)

Проблема до 3NF: В таблице Orders атрибуты клиента (имя, город и прочие данные) зависят от order_id лишь косвенно. На самом деле они зависят от личности клиента. Если один клиент сделал несколько заказов, его имя/город повторяются в каждой строке Orders. Получается избыточность и потенциальные аномалии: например, если клиент поменяет город, придется менять это в каждом его заказе. Или, удалив заказ, мы потеряем данные о клиенте, если он больше не имеет заказов.

Определение 3NF: Таблица находится в третьей нормальной форме, если выполнены условия 2NF и ни один неключевой столбец не зависит транзитивно от первичного ключа. Формально: не должно существовать такой цепочки зависимостей Key -> X -> Y, где X и Y – неключевые столбцы. Другими словами, все атрибуты должны определяться напрямую ключом, а не через другие атрибуты. В 3NF также вводится понятие ключей-кандидатов и требование, что каждый неключевой атрибут зависит только от ключа (первичного или альтернативного) и ни от чего более.

Как добиться 3NF: Нужно вынести независимые сущности, вызывающие транзитивные зависимости, в отдельные таблицы. В нашем случае очевидно: следует создать таблицу Customers для данных о клиентах. В ней будет customer_id (первичный ключ), name, city, и другие данные о покупателе. Таблица Orders вместо хранения имени и города будет содержать внешний ключ customer_id, ссылающийся на запись в Customers.

Рис. 4: схема после приведения к 3NF – выделена таблица Customers.

Теперь данные о клиентах не дублируются – каждый клиент хранится один раз в таблице Customers, а заказы ссылаются на него по customer_id. Наша схема достигла третьей нормальной формы: таблицы Orders, Products, Customers не содержат ни повторяющихся групп, ни частичных, ни транзитивных зависимостей. Каждая таблица описывает одну сущность: заказ, товар, клиент, а таблица OrderItems связывает заказы с товарами (реализуя многие-ко-многим связь). В таком дизайне база данных уже не страдает от основных видов аномалий: обновить название товара или адрес клиента можно в одном месте; вставить новый товар или клиента можно независимо; удаление заказа не уничтожит сведения о товарах или покупателях, т.к. они хранятся отдельно и могут участвовать в других заказах.

На практике большинство баз данных доводят до 3NF (или до НФ Бойса-Кодда, о которой далее) и этого достаточно. Однако существуют более строгие нормальные формы для редких случаев.

DDL (PostgreSQL)

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    customer_name VARCHAR,
    customer_city VARCHAR
);

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE,
    customer_id INT REFERENCES customers(customer_id)
);

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR,
    product_price DECIMAL
);

CREATE TABLE order_items (
    item_id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(order_id),
    product_id INT REFERENCES products(product_id),
    quantity INT
);

Java (Spring Data JPA)

@Entity @Table(name = "customers")
class Customer {
    @Id @GeneratedValue
    private Long customerId;
    private String customerName;
    private String customerCity;
}

@Entity @Table(name = "orders")
class Order {
    @Id @GeneratedValue
    private Long orderId;
    private LocalDate orderDate;

    @ManyToOne
    @JoinColumn(name = "customer_id")
    private Customer customer;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "order")
    private List<OrderItem> items = new ArrayList<>();
}

@Entity @Table(name = "products")
class Product {
    @Id @GeneratedValue
    private Long productId;
    private String productName;
    private BigDecimal productPrice;
}

@Entity @Table(name = "order_items")
class OrderItem {
    @Id @GeneratedValue
    private Long itemId;
    private int quantity;

    @ManyToOne
    @JoinColumn(name = "order_id")
    private Order order;

    @ManyToOne
    @JoinColumn(name = "product_id")
    private Product product;
}

interface CustomerRepo extends JpaRepository<Customer, Long> {}
interface OrderRepo extends JpaRepository<Order, Long> {}
interface ProductRepo extends JpaRepository<Product, Long> {}

@Service
class OrderService {
    @Autowired private OrderRepo orderRepo;
    @Autowired private ProductRepo productRepo;
    @Autowired private CustomerRepo customerRepo;

    @Transactional
    public void createOrder() {
        Customer customer = new Customer();
        customer.setCustomerName("Иван Иванов");
        customer.setCustomerCity("Москва");
        customerRepo.save(customer);

        Product phone = new Product();
        phone.setProductName("Телефон");
        phone.setProductPrice(new BigDecimal("25000"));
        productRepo.save(phone);

        Order order = new Order();
        order.setOrderDate(LocalDate.now());
        order.setCustomer(customer);

        OrderItem item = new OrderItem();
        item.setOrder(order);
        item.setProduct(phone);
        item.setQuantity(1);

        order.setItems(List.of(item));
        orderRepo.save(order);
    }
}

Go (pgx)

package main

import (
    "context"
    "fmt"
    "log"
    "time"

    "github.com/jackc/pgx/v5"
)

type Customer struct {
    CustomerID   int
    CustomerName string
    CustomerCity string
}

type Order struct {
    OrderID    int
    OrderDate  time.Time
    CustomerID int
    Customer   Customer
    Items      []OrderItem
}

type Product struct {
    ProductID    int
    ProductName  string
    ProductPrice float64
}

type OrderItem struct {
    ItemID    int
    OrderID   int
    ProductID int
    Quantity  int
    Product   Product
}

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
    if err != nil { log.Fatal(err) }
    defer conn.Close(ctx)

    createTables(ctx, conn)

    customerID := insertCustomer(ctx, conn, "Иван Иванов", "Москва")
    phoneID := insertProduct(ctx, conn, "Телефон", 25000)

    order := Order{
        OrderDate:  time.Now(),
        CustomerID: customerID,
        Items: []OrderItem{
            {ProductID: phoneID, Quantity: 1},
        },
    }

    saveOrder(ctx, conn, &order)
    printOrders(ctx, conn)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
    conn.Exec(ctx, `
    CREATE TABLE IF NOT EXISTS customers(
        customer_id SERIAL PRIMARY KEY, customer_name VARCHAR, customer_city VARCHAR);
    CREATE TABLE IF NOT EXISTS orders(
        order_id SERIAL PRIMARY KEY, order_date DATE, customer_id INT REFERENCES customers);
    CREATE TABLE IF NOT EXISTS products(
        product_id SERIAL PRIMARY KEY, product_name VARCHAR, product_price DECIMAL);
    CREATE TABLE IF NOT EXISTS order_items(
        item_id SERIAL PRIMARY KEY, order_id INT REFERENCES orders,
        product_id INT REFERENCES products, quantity INT);`)
}

func insertCustomer(ctx context.Context, conn *pgx.Conn, name, city string) int {
    var id int
    conn.QueryRow(ctx, `INSERT INTO customers(customer_name, customer_city) 
                        VALUES($1,$2) RETURNING customer_id`, name, city).Scan(&id)
    return id
}

func insertProduct(ctx context.Context, conn *pgx.Conn, name string, price float64) int {
    var id int
    conn.QueryRow(ctx, `INSERT INTO products(product_name, product_price) 
                        VALUES($1,$2) RETURNING product_id`, name, price).Scan(&id)
    return id
}

func saveOrder(ctx context.Context, conn *pgx.Conn, order *Order) {
    conn.QueryRow(ctx, `INSERT INTO orders(order_date, customer_id) 
                        VALUES($1,$2) RETURNING order_id`,
        order.OrderDate, order.CustomerID).Scan(&order.OrderID)

    for i := range order.Items {
        conn.QueryRow(ctx, `INSERT INTO order_items(order_id, product_id, quantity) 
                            VALUES($1,$2,$3) RETURNING item_id`,
            order.OrderID, order.Items[i].ProductID, order.Items[i].Quantity).Scan(&order.Items[i].ItemID)
    }
}

func printOrders(ctx context.Context, conn *pgx.Conn) {
    rows, _ := conn.Query(ctx, `
    SELECT o.order_id, o.order_date, c.customer_name, c.customer_city,
           i.item_id, p.product_name, p.product_price, i.quantity
    FROM orders o
    JOIN customers c ON o.customer_id = c.customer_id
    JOIN order_items i ON o.order_id = i.order_id
    JOIN products p ON i.product_id = p.product_id ORDER BY o.order_id`)

    defer rows.Close()
    for rows.Next() {
        var o Order
        var i OrderItem
        rows.Scan(&o.OrderID, &o.OrderDate, &o.Customer.CustomerName, &o.Customer.CustomerCity,
            &i.ItemID, &i.Product.ProductName, &i.Product.ProductPrice, &i.Quantity)
        fmt.Printf("Заказ #%d (%s), клиент: %s (%s)\n- %s: %d шт., %.2f руб.\n",
            o.OrderID, o.OrderDate.Format("02.01.2006"), o.Customer.CustomerName, o.Customer.CustomerCity,
            i.Product.ProductName, i.Quantity, i.Product.ProductPrice)
    }
}

Нормальная форма Бойса-Кодда (BCNF)

НФ Бойса-Кодда считается улучшенной версией 3NF. Формальное определение: таблица в BCNF, если на каждом функциональном зависимости X -> Y, X является суперключом (т.е. либо самим ключом, либо надмножеством ключа). Проще говоря, детерминантами в таблице могут быть только ключи. BCNF решает некоторые особые случаи, которые 3NF пропускает. Например, если в таблице несколько кандидатных ключей, 3NF допускает ситуацию, когда атрибут зависит от части альтернативного ключа (что может вызывать аномалии) – BCNF это запрещает.

Пример BCNF: представьте таблицу расписания занятий со столбцами: курс, преподаватель, аудитория. Предположим, ключ – составной (курс, аудитория) (курс в определенной аудитории уникален). Но также действует правило: каждый курс ведёт один преподаватель, и каждый преподаватель проводит занятия только по одному курсу. Тогда у нас функциональная зависимость курс -> преподаватель и преподаватель -> курс. Здесь кандидаты на ключи: (курс, аудитория) и (преподаватель, аудитория). Эта таблица в 3NF может и находится, но имеет аномалии: например, нельзя добавить нового преподавателя, пока не назначен курс и аудитория. Решение – разбить на две таблицы: “курс – преподаватель” и “преподаватель – аудитория”. Таким образом, BCNF убирает оставшиеся зависимости, разделив таблицу.

Рис. 5: схема таблиц для BCNF

В реальных задачах встретить нарушение 3NF, требующее BCNF, можно нечасто, но нужно знать: BCNF = 3NF без исключений. Если ваша схема в BCNF, то уж точно никаких функциональных аномалий не осталось.

DDL (PostgreSQL)

CREATE TABLE teachers (
    teacher_id SERIAL PRIMARY KEY,
    teacher_name VARCHAR
);

CREATE TABLE rooms (
    room_id SERIAL PRIMARY KEY,
    room_name VARCHAR
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    course_name VARCHAR,
    teacher_id INT REFERENCES teachers(teacher_id),
    room_id INT REFERENCES rooms(room_id),
    UNIQUE(teacher_id, room_id)
);

Java (Spring Data JPA)

@Entity @Table(name = "teachers")
class Teacher {
    @Id @GeneratedValue
    private Long teacherId;
    private String teacherName;
}

@Entity @Table(name = "rooms")
class Room {
    @Id @GeneratedValue
    private Long roomId;
    private String roomName;
}

@Entity @Table(name = "courses", uniqueConstraints = {
    @UniqueConstraint(columnNames = {"teacher_id", "room_id"})
})
class Course {
    @Id @GeneratedValue
    private Long courseId;
    private String courseName;

    @ManyToOne
    @JoinColumn(name = "teacher_id")
    private Teacher teacher;

    @ManyToOne
    @JoinColumn(name = "room_id")
    private Room room;
}

interface TeacherRepo extends JpaRepository<Teacher, Long> {}
interface RoomRepo extends JpaRepository<Room, Long> {}
interface CourseRepo extends JpaRepository<Course, Long> {}

@Service
class CourseService {
    @Autowired TeacherRepo teacherRepo;
    @Autowired RoomRepo roomRepo;
    @Autowired CourseRepo courseRepo;

    @Transactional
    public void createCourse() {
        Teacher teacher = new Teacher();
        teacher.setTeacherName("Профессор Иванов");
        teacherRepo.save(teacher);

        Room room = new Room();
        room.setRoomName("Аудитория 101");
        roomRepo.save(room);

        Course course = new Course();
        course.setCourseName("Основы Java");
        course.setTeacher(teacher);
        course.setRoom(room);
        courseRepo.save(course);
    }

    @Transactional(readOnly=true)
    public void printCourses() {
        courseRepo.findAll().forEach(c -> 
            System.out.printf("Курс: %s, Преподаватель: %s, Аудитория: %s%n",
                c.getCourseName(), c.getTeacher().getTeacherName(), c.getRoom().getRoomName()));
    }
}

Go (pgx)

package main

import (
	"context"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
)

type Teacher struct {
	ID   int
	Name string
}

type Room struct {
	ID   int
	Name string
}

type Course struct {
	ID        int
	Name      string
	TeacherID int
	RoomID    int
	Teacher   Teacher
	Room      Room
}

func main() {
	ctx := context.Background()
	conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
	if err != nil { log.Fatal(err) }
	defer conn.Close(ctx)

	createTables(ctx, conn)

	teacherID := insertTeacher(ctx, conn, "Профессор Иванов")
	roomID := insertRoom(ctx, conn, "Аудитория 101")

	courseID := insertCourse(ctx, conn, "Основы Go", teacherID, roomID)
	fmt.Printf("Создан курс #%d\n", courseID)

	printCourses(ctx, conn)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
	conn.Exec(ctx, `
	CREATE TABLE IF NOT EXISTS teachers(
		teacher_id SERIAL PRIMARY KEY, teacher_name VARCHAR);
	CREATE TABLE IF NOT EXISTS rooms(
		room_id SERIAL PRIMARY KEY, room_name VARCHAR);
	CREATE TABLE IF NOT EXISTS courses(
		course_id SERIAL PRIMARY KEY, course_name VARCHAR,
		teacher_id INT REFERENCES teachers, 
		room_id INT REFERENCES rooms,
		UNIQUE(teacher_id, room_id));`)
}

func insertTeacher(ctx context.Context, conn *pgx.Conn, name string) int {
	var id int
	conn.QueryRow(ctx, `INSERT INTO teachers(teacher_name) VALUES($1) RETURNING teacher_id`, name).Scan(&id)
	return id
}

func insertRoom(ctx context.Context, conn *pgx.Conn, name string) int {
	var id int
	conn.QueryRow(ctx, `INSERT INTO rooms(room_name) VALUES($1) RETURNING room_id`, name).Scan(&id)
	return id
}

func insertCourse(ctx context.Context, conn *pgx.Conn, name string, teacherID, roomID int) int {
	var id int
	conn.QueryRow(ctx, `
	INSERT INTO courses(course_name, teacher_id, room_id) 
	VALUES($1,$2,$3) RETURNING course_id`,
		name, teacherID, roomID).Scan(&id)
	return id
}

func printCourses(ctx context.Context, conn *pgx.Conn) {
	rows, _ := conn.Query(ctx, `
	SELECT c.course_id, c.course_name, t.teacher_name, r.room_name
	FROM courses c
	JOIN teachers t ON c.teacher_id = t.teacher_id
	JOIN rooms r ON c.room_id = r.room_id ORDER BY c.course_id`)

	defer rows.Close()
	for rows.Next() {
		var c Course
		rows.Scan(&c.ID, &c.Name, &c.Teacher.Name, &c.Room.Name)
		fmt.Printf("Курс: %s, Преподаватель: %s, Аудитория: %s\n",
			c.Name, c.Teacher.Name, c.Room.Name)
	}
}

Четвертая нормальная форма (4NF)

К четвертой нормальной форме переходят, решив все проблемы с функциональными зависимостями (1NF-BCNF) и столкнувшись с более сложным типом зависимости – многозначной зависимостью. Многозначная зависимость (multi-valued dependency) возникает, когда одна сущность связана с двумя или более независимыми списками значений.

Представьте, что в нашем интернет-магазине у товара может быть несколько категорий (например, “электроника” и “распродажа”) и одновременно несколько тегов или вариантов (например, теги “новинка”, “скидка”). Если попытаться хранить категории и теги товаров вместе, возникнет избыточность. Например, можно завести таблицу ProductCategoryTag(product_id, category, tag). Первичный ключ здесь составной из всех трех полей. В такой таблице каждая комбинация категории и тега для товара будет отдельной строкой, что приводит к дублированию: один и тот же товар с категорией A и разными тегами будет повторять категорию A в каждой строке, и наоборот тег B повторится для всех категорий товара.

Таблица, нарушающая 4NF: соединяет независимые списки (категории и теги товара). Здесь product_id связан с двумя множествами: множеством категорий и множеством тегов. Категории и теги независимы друг от друга, но таблица хранит их комбинации, что ведет к множеству лишних строк. Если у товара 3 категории и 5 тегов, в такой таблице будет 15 строк, хотя по сути информация содержится в двух списках 3 и 5 элементов.

Определение 4NF: Таблица находится в четвертой нормальной форме, если она в BCNF и не содержит нетривиальных многозначных зависимостей. Практически это означает: если сущность имеет несколько независимых списков атрибутов, их нужно вынести в разные таблицы. Каждая таблица должна представлять одну многозначную зависимость, а не смешивать их.

Как добиться 4NF: Разделить таблицу, содержащую два (или более) независимых множества, на две таблицы. В нашем примере продукт имеет список категорий и список тегов. Решение: создать отдельно ProductCategory(product_id, category) и отдельно ProductTag(product_id, tag). Тогда каждая таблица отражает одну связь многие-ко-многим: товары-категории и товары-теги.

Рис. 6: cхема после приведения к 4NF – категории и теги разделены.

Теперь, чтобы указать что товар принадлежит 3 категориям и 5 тегам, мы храним всего 3 записи в таблице категорий и 5 записей в таблице тегов (вместо 15 комбинаций). Мы устранили многозначные зависимости: категория товара хранится независимо от тегов, и наоборот. Таблицы ProductCategory и ProductTag не имеют собственных составных ключей – каждое отношение “товар – категория” или “товар – тег” представлено отдельно.

На практике 4NF может потребоваться, когда модель данных действительно предполагает несколько независимых наборов атрибутов у одной сущности. Если таких случаев нет, то, доведя базу до 3NF/BCNF, вы уже избегаете большинства проблем. Но знать о 4NF полезно, чтобы распознать ситуации, где одно сущность лучше разложить на несколько связующих таблиц.

DDL (PostgreSQL)

CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    product_name VARCHAR
);

CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    category_name VARCHAR
);

CREATE TABLE tags (
    tag_id SERIAL PRIMARY KEY,
    tag_name VARCHAR
);

CREATE TABLE product_categories (
    product_id INT REFERENCES products(product_id),
    category_id INT REFERENCES categories(category_id),
    PRIMARY KEY(product_id, category_id)
);

CREATE TABLE product_tags (
    product_id INT REFERENCES products(product_id),
    tag_id INT REFERENCES tags(tag_id),
    PRIMARY KEY(product_id, tag_id)
);

Java (Spring Data JPA)

@Entity @Table(name = "products")
class Product {
    @Id @GeneratedValue
    private Long productId;
    private String productName;
}

@Entity @Table(name = "categories")
class Category {
    @Id @GeneratedValue
    private Long categoryId;
    private String categoryName;
}

@Entity @Table(name = "tags")
class Tag {
    @Id @GeneratedValue
    private Long tagId;
    private String tagName;
}

@Entity @Table(name = "product_categories")
class ProductCategory {
    @EmbeddedId
    private ProductCategoryId id;

    @ManyToOne @MapsId("productId")
    private Product product;

    @ManyToOne @MapsId("categoryId")
    private Category category;
}

@Embeddable
class ProductCategoryId implements Serializable {
    private Long productId;
    private Long categoryId;
}

@Entity @Table(name = "product_tags")
class ProductTag {
    @EmbeddedId
    private ProductTagId id;

    @ManyToOne @MapsId("productId")
    private Product product;

    @ManyToOne @MapsId("tagId")
    private Tag tag;
}

@Embeddable
class ProductTagId implements Serializable {
    private Long productId;
    private Long tagId;
}

interface ProductRepo extends JpaRepository<Product, Long> {}
interface CategoryRepo extends JpaRepository<Category, Long> {}
interface TagRepo extends JpaRepository<Tag, Long> {}
interface ProductCategoryRepo extends JpaRepository<ProductCategory, ProductCategoryId> {}
interface ProductTagRepo extends JpaRepository<ProductTag, ProductTagId> {}

@Service
class ProductService {
    @Autowired private ProductRepo productRepo;
    @Autowired private CategoryRepo categoryRepo;
    @Autowired private TagRepo tagRepo;
    @Autowired private ProductCategoryRepo productCategoryRepo;
    @Autowired private ProductTagRepo productTagRepo;

    @Transactional
    public void createProductWithCategoryAndTag() {
        Product product = new Product();
        product.setProductName("Телевизор");
        productRepo.save(product);

        Category category = new Category();
        category.setCategoryName("Электроника");
        categoryRepo.save(category);

        Tag tag = new Tag();
        tag.setTagName("Новинка");
        tagRepo.save(tag);

        ProductCategory pc = new ProductCategory();
        pc.setId(new ProductCategoryId(product.getProductId(), category.getCategoryId()));
        pc.setProduct(product);
        pc.setCategory(category);
        productCategoryRepo.save(pc);

        ProductTag pt = new ProductTag();
        pt.setId(new ProductTagId(product.getProductId(), tag.getTagId()));
        pt.setProduct(product);
        pt.setTag(tag);
        productTagRepo.save(pt);
    }
}

Go (pgx)

package main

import (
    "context"
    "log"

    "github.com/jackc/pgx/v5"
)

type Product struct {
    ID   int
    Name string
}

type Category struct {
    ID   int
    Name string
}

type Tag struct {
    ID   int
    Name string
}

func main() {
    ctx := context.Background()
    conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
    if err != nil { log.Fatal(err) }
    defer conn.Close(ctx)

    createTables(ctx, conn)

    productID := insertProduct(ctx, conn, "Телевизор")
    categoryID := insertCategory(ctx, conn, "Электроника")
    tagID := insertTag(ctx, conn, "Новинка")

    insertProductCategory(ctx, conn, productID, categoryID)
    insertProductTag(ctx, conn, productID, tagID)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
    conn.Exec(ctx, `
    CREATE TABLE IF NOT EXISTS products(
        product_id SERIAL PRIMARY KEY, product_name VARCHAR);
    CREATE TABLE IF NOT EXISTS categories(
        category_id SERIAL PRIMARY KEY, category_name VARCHAR);
    CREATE TABLE IF NOT EXISTS tags(
        tag_id SERIAL PRIMARY KEY, tag_name VARCHAR);
    CREATE TABLE IF NOT EXISTS product_categories(
        product_id INT REFERENCES products, 
        category_id INT REFERENCES categories,
        PRIMARY KEY(product_id, category_id));
    CREATE TABLE IF NOT EXISTS product_tags(
        product_id INT REFERENCES products, 
        tag_id INT REFERENCES tags,
        PRIMARY KEY(product_id, tag_id));`)
}

func insertProduct(ctx context.Context, conn *pgx.Conn, name string) int {
    var id int
    conn.QueryRow(ctx, `
    INSERT INTO products(product_name) VALUES($1) RETURNING product_id`, name).Scan(&id)
    return id
}

func insertCategory(ctx context.Context, conn *pgx.Conn, name string) int {
    var id int
    conn.QueryRow(ctx, `
    INSERT INTO categories(category_name) VALUES($1) RETURNING category_id`, name).Scan(&id)
    return id
}

func insertTag(ctx context.Context, conn *pgx.Conn, name string) int {
    var id int
    conn.QueryRow(ctx, `
    INSERT INTO tags(tag_name) VALUES($1) RETURNING tag_id`, name).Scan(&id)
    return id
}

func insertProductCategory(ctx context.Context, conn *pgx.Conn, pid, cid int) {
    conn.Exec(ctx, `
    INSERT INTO product_categories(product_id, category_id) VALUES($1, $2)`, pid, cid)
}

func insertProductTag(ctx context.Context, conn *pgx.Conn, pid, tid int) {
    conn.Exec(ctx, `
    INSERT INTO product_tags(product_id, tag_id) VALUES($1, $2)`, pid, tid)
}

Пятая нормальная форма (5NF)

Пятая нормальная форма (также PJNFProjection-Join Normal Form, проекционно-соединительная НФ) – высшая на сегодняшний день ступень нормализации. Она имеет дело с еще более редким случаем – зависимостями соединения (join dependencies). 5NF требуется, когда информацию в таблице невозможно разложить на две части без потери данных, но можно разложить на три и более частей.

Звучит сложно, рассмотрим пример. Допустим, есть таблица, содержащая сочетание трех сущностей: SupplierPartConsumer – она фиксирует, что некоторый поставщик (supplier) поставляет определенную деталь (part) для определенного клиента/покупателя (consumer). Первичный ключ – все три поля вместе (поставщик+деталь+покупатель). Предположим, бизнес-правила таковы, что:

  • Каждый поставщик может поставлять различные детали (список поставщик-деталь).
  • Каждому клиенту требуются различные детали (список деталь-клиент).
  • Каждый поставщик работает с разными клиентами (список поставщик-клиент).

Если все эти пары хранятся отдельно, то факт того, что конкретный поставщик поставляет конкретную деталь конкретному клиенту, может быть выведен из комбинации этих списков. Получается, что большая таблица SupplierPartConsumer избыточна – она получается соединением трех таблиц: SupplierPart, PartConsumer и SupplierConsumer. Это и есть зависимость соединения: таблица разлагается на несколько так, что исходные данные можно восстановить только через соединение всех частей.

Таблица, нарушающая 5NF: сочетает три сущности (Supplier, Part, Consumer). В этой таблице каждая запись – тройка (поставщик, деталь, покупатель). Предположим, существуют отдельные зависимости: какие детали поставляет каждый поставщик; какие детали нужны каждому покупателю; с какими покупателями работает поставщик. Тогда таблица тройных комбинаций будет во многом повторять информацию из этих зависимостей.

Определение 5NF: Таблица в 5NF, если она в 4NF и не распадается на несколько таблиц, при соединении которых без потерь получается исходная таблица. Иными словами, никакой непустой набор ее атрибутов не обладает нетривиальной зависимостью соединения. 5NF устраняет избыточность, возникающую исключительно из-за сложных связей между тремя и более таблицами.

Как добиться 5NF: Разбить таблицу на наименьшие части, из которых она составляется соединением. В нашем примере решение – хранить отдельно:

  • таблицу SupplierPart(supplier, part) – какие детали поставляет каждый поставщик;
  • таблицу PartConsumer(part, consumer) – какие детали нужны каждому покупателю;
  • таблицу SupplierConsumer(supplier, consumer) – какие поставщики работают с какими покупателями.

Рис. 7: cхема после приведения к 5NF: тройная связь разложена на три двойных.

После декомпозиции каждая из трех таблиц находится в 4NF (в них по два атрибута) и не содержит избыточности. Исходную информацию о том, что поставщик X поставляет деталь Y для клиента Z, мы можем получить, выполнив JOIN всех трех таблиц на соответствующих полях. В данном случае разбиение устраняет дублирование: например, факт, что поставщик S1 и клиент C1 работают друг с другом, хранится единожды (в SupplierConsumer), а раньше повторялся для каждой детали, которую S1 поставляет C1.

Стоит отметить: 5NF практически не встречается в прикладных системах. Ситуации с такими сложными зависимостями редки, и зачастую если в модели появилась настолько сложная связь, это повод пересмотреть саму модель. Тем не менее, знать про 5NF полезно с теоретической точки зрения – она гарантирует максимально полное устранение дублирования данных. В реальной работе чаще всего используются 3NF/BCNF, а 4NF и 5NF остаются в учебниках или специфичных задачах.

DDL (PostgreSQL)

CREATE TABLE suppliers (
  supplier_id SERIAL PRIMARY KEY,
  supplier_name VARCHAR
);

CREATE TABLE parts (
  part_id SERIAL PRIMARY KEY,
  part_name VARCHAR
);

CREATE TABLE consumers (
  consumer_id SERIAL PRIMARY KEY,
  consumer_name VARCHAR
);

CREATE TABLE supplier_parts (
  supplier_id INT REFERENCES suppliers(supplier_id),
  part_id INT REFERENCES parts(part_id),
  PRIMARY KEY(supplier_id, part_id)
);

CREATE TABLE part_consumers (
  part_id INT REFERENCES parts(part_id),
  consumer_id INT REFERENCES consumers(consumer_id),
  PRIMARY KEY(part_id, consumer_id)
);

CREATE TABLE supplier_consumers (
  supplier_id INT REFERENCES suppliers(supplier_id),
  consumer_id INT REFERENCES consumers(consumer_id),
  PRIMARY KEY(supplier_id, consumer_id)
);

Java (Spring Data JPA)

@Entity @Table(name="suppliers")
class Supplier {
  @Id @GeneratedValue
  private Long supplierId;
  private String supplierName;
}

@Entity @Table(name="parts")
class Part {
  @Id @GeneratedValue
  private Long partId;
  private String partName;
}

@Entity @Table(name="consumers")
class Consumer {
  @Id @GeneratedValue
  private Long consumerId;
  private String consumerName;
}

@Entity @Table(name="supplier_parts")
class SupplierPart {
  @EmbeddedId
  private SupplierPartId id;

  @ManyToOne @MapsId("supplierId")
  private Supplier supplier;

  @ManyToOne @MapsId("partId")
  private Part part;
}

@Embeddable
class SupplierPartId implements Serializable {
  private Long supplierId;
  private Long partId;
}

@Entity @Table(name="part_consumers")
class PartConsumer {
  @EmbeddedId
  private PartConsumerId id;

  @ManyToOne @MapsId("partId")
  private Part part;

  @ManyToOne @MapsId("consumerId")
  private Consumer consumer;
}

@Embeddable
class PartConsumerId implements Serializable {
  private Long partId;
  private Long consumerId;
}

@Entity @Table(name="supplier_consumers")
class SupplierConsumer {
  @EmbeddedId
  private SupplierConsumerId id;

  @ManyToOne @MapsId("supplierId")
  private Supplier supplier;

  @ManyToOne @MapsId("consumerId")
  private Consumer consumer;
}

@Embeddable
class SupplierConsumerId implements Serializable {
  private Long supplierId;
  private Long consumerId;
}

interface SupplierRepo extends JpaRepository<Supplier, Long> {}
interface PartRepo extends JpaRepository<Part, Long> {}
interface ConsumerRepo extends JpaRepository<Consumer, Long> {}
interface SupplierPartRepo extends JpaRepository<SupplierPart, SupplierPartId> {}
interface PartConsumerRepo extends JpaRepository<PartConsumer, PartConsumerId> {}
interface SupplierConsumerRepo extends JpaRepository<SupplierConsumer, SupplierConsumerId> {}

@Service
class SupplyService {
  @Autowired SupplierRepo supplierRepo;
  @Autowired PartRepo partRepo;
  @Autowired ConsumerRepo consumerRepo;
  @Autowired SupplierPartRepo supplierPartRepo;
  @Autowired PartConsumerRepo partConsumerRepo;
  @Autowired SupplierConsumerRepo supplierConsumerRepo;

  @Transactional
  public void setupRelations() {
    Supplier s = supplierRepo.save(new Supplier("Supplier A"));
    Part p = partRepo.save(new Part("Part X"));
    Consumer c = consumerRepo.save(new Consumer("Consumer 1"));

    supplierPartRepo.save(new SupplierPart(new SupplierPartId(s.getSupplierId(), p.getPartId()), s, p));
    partConsumerRepo.save(new PartConsumer(new PartConsumerId(p.getPartId(), c.getConsumerId()), p, c));
    supplierConsumerRepo.save(new SupplierConsumer(new SupplierConsumerId(s.getSupplierId(), c.getConsumerId()), s, c));
  }
}

Go (pgx)

package main

import (
  "context"
  "log"
  "github.com/jackc/pgx/v5"
)

type Supplier struct { ID int; Name string }
type Part struct { ID int; Name string }
type Consumer struct { ID int; Name string }

func main() {
  ctx := context.Background()
  conn, err := pgx.Connect(ctx, "postgres://postgres:password@localhost:5432/mydb")
  if err != nil { log.Fatal(err) }
  defer conn.Close(ctx)

  createTables(ctx, conn)

  sID := insert(ctx, conn, "suppliers", "supplier_name", "Supplier A")
  pID := insert(ctx, conn, "parts", "part_name", "Part X")
  cID := insert(ctx, conn, "consumers", "consumer_name", "Consumer 1")

  insertRelation(ctx, conn, "supplier_parts", "supplier_id", "part_id", sID, pID)
  insertRelation(ctx, conn, "part_consumers", "part_id", "consumer_id", pID, cID)
  insertRelation(ctx, conn, "supplier_consumers", "supplier_id", "consumer_id", sID, cID)
}

func createTables(ctx context.Context, conn *pgx.Conn) {
  conn.Exec(ctx, `
    CREATE TABLE IF NOT EXISTS suppliers(supplier_id SERIAL PRIMARY KEY, supplier_name VARCHAR);
    CREATE TABLE IF NOT EXISTS parts(part_id SERIAL PRIMARY KEY, part_name VARCHAR);
    CREATE TABLE IF NOT EXISTS consumers(consumer_id SERIAL PRIMARY KEY, consumer_name VARCHAR);
    CREATE TABLE IF NOT EXISTS supplier_parts(supplier_id INT REFERENCES suppliers, part_id INT REFERENCES parts, PRIMARY KEY(supplier_id, part_id));
    CREATE TABLE IF NOT EXISTS part_consumers(part_id INT REFERENCES parts, consumer_id INT REFERENCES consumers, PRIMARY KEY(part_id, consumer_id));
    CREATE TABLE IF NOT EXISTS supplier_consumers(supplier_id INT REFERENCES suppliers, consumer_id INT REFERENCES consumers, PRIMARY KEY(supplier_id, consumer_id));
  `)
}

func insert(ctx context.Context, conn *pgx.Conn, table, field, value string) int {
  var id int
  conn.QueryRow(ctx, "INSERT INTO "+table+"("+field+") VALUES($1) RETURNING "+table[:len(table)-1]+"_id", value).Scan(&id)
  return id
}

func insertRelation(ctx context.Context, conn *pgx.Conn, table, f1, f2 string, id1, id2 int) {
  conn.Exec(ctx, "INSERT INTO "+table+"("+f1+","+f2+") VALUES($1,$2)", id1, id2)
}

Заключение

Нормализация базы данных – это важный и фундаментальный процесс проектирования эффективных и отказоустойчивых систем. В данной статье мы последовательно рассмотрели и на практике применили ключевые этапы нормализации – от первой нормальной формы (1NF) до пятой нормальной формы (5NF).

На примерах интернет-магазина, расписания занятий и поставок деталей мы продемонстрировали, как переход от одной нормальной формы к другой позволяет избежать распространённых ошибок проектирования: дублирования данных, некорректного хранения сущностей и сложных зависимостей между атрибутами.

Каждая из рассмотренных нормальных форм (1NF, 2NF, 3NF, BCNF, 4NF и 5NF) имеет своё предназначение и область применения. Чаще всего на практике бывает достаточно привести базу данных к третьей нормальной форме или нормальной форме Бойса-Кодда, так как это эффективно предотвращает большинство проблем с избыточностью и аномалиями данных. Тем не менее, знание более строгих форм, таких как 4NF и 5NF, существенно расширяет понимание реляционных моделей и помогает проектировать сложные системы с большим количеством взаимосвязанных сущностей.

Представленные примеры на ERD-диаграммах, а также практические реализации в виде DDL-скриптов и примеров кода на Java (Spring Data JPA) и Go (pgx) помогут начинающим backend-разработчикам глубже усвоить принципы нормализации и применять их в реальных проектах.

Следование правилам нормализации обеспечивает:

  • Целостность и надёжность данных;
  • Упрощение поддержки и масштабирования приложений;
  • Повышение производительности системы за счёт эффективной структуры таблиц и индексов.

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

Loading