Котики, кто на ЛАФ? 😘

♻️ Концептуальная модель данных ♻️

Продолжаем последовательно делать нашу задачу. Теперь рассмотрим связи между ранее перечисленными сущностями. Это будет первой частью концептуальной модели данных.

➿ Книга и️ Автор
Один автор может написать много книг
Одна книга также может иметь нескольких авторов.
связь «многие ко многим»


➿ Книга и️ Жанр
Книга может относиться сразу к нескольким жанрам
связь «многие ко многим»

➿ Книга и️ Отдел
Книги располагаются в определенных отделах библиотеки: один отдел хранит много книг, но каждая книга находится лишь в одном отделе.
связь «один ко многим»

➿ Книга и Запись о выдаче
Каждая запись о выдаче связана с конкретной книгой: одна книга может многократно выдаваться разным пользователям, но конкретная запись относится именно к одной книге.
связь «один ко многим»

➿ Пользователь и️ Запись о выдаче
Пользователи берут разные книги в разное время: каждый пользователь может брать много книг, но отдельная запись принадлежит одному пользователю.
связь «один ко многим».

➿ Запись о выдаче и Штрафы
Если пользователь просрочил возвращение книги, появляется задолженность: одна запись о выдаче может привести к начислению одного или нескольких штрафов, но штраф всегда привязывается к конкретной записи о выдаче.
связь «один ко многим»

Следующий этап — выбор подходящей структуры хранения данных.

♻️ Концептуальная модель данных ♻️

🤜 Проанализируем тип структуры

Какие аспекты нужно учесть:
▪️наличие большого количества стандартных сущностей (книги, клиенты, штрафы);
▪️необходимость обработки отношений между этими сущностями (книга связана с выдачей клиенту, выдача связана с записью о возврате);
▪️важность гарантии целостности данных при проведении различных операций.

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


🤜 Определяем атрибуты

Для нашей несложной задачи выделим следующие таблицы и поля:

📖 Books (Книги)
- title (название)
- isbn (ISBN-код)
- authors (авторы книги) – может быть > 1
- publicationyear (год издания)
- pagescount (количество страниц)
- availabilitystatus (статус доступности)
- location (местоположение/полка)
- genre (жанр). Может быть > 1)

🧑‍🎓 Authors (Авторы)
- fullname (ФИО автора)
- biography (биография)

🧞‍♂️ Genres (Жанры)
- name (жанр)

🏛 Departments (Отделы)
- name (название отдела)

🙍 Users (Читатели)
- firstname (имя)
- lastname (фамилия)
- readerticketnumber (номер читательского билета)
- phonenumber (телефон)
- email (электронная почта)
- address (адрес проживания)

🎫 Loans (Выдачи книг)
- book (Выданная книга)
- user (Пользователь, который ее взял)
- issuedate (дата выдачи)
- returndate (предполагаемая дата возврата)
- actualreturndate (фактическая дата возврата)
– loantype (тип выдачи - читальный зал или на дом)

💸 Fines (Штрафы)
- loan (Запись о выдаче)
- amount (сумма штрафа)
- reason (причина начисления штрафа)
- status (статус, оплачен или нет)

Далее займемся приведением данных к нормальным формам.

Не забывайте правильно оценивать таски!)

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

Наши дальнейшие преобразования покажутся вам очень простыми и очевидными, и мы могли их сделать еще 3 поста назад. Не забываем, что цель нашего разбора – просто вспомнить, что такое нормальные формы и чем они отличаются друг от друга, чтобы удовлетворить самого въедливого интервьюера на собесе :) (или просто для общего развития)

📀 Первая нормальная форма (1NF): Уничтожение повторяющихся групп.

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

⏳ Проблема: Поле authors может содержать больше одного значения (авторов). Это нарушает правило 1-й НФ, поскольку должно быть одно значение на одну строку.

⌛️Решение: Разбиваем этот атрибут на отдельную таблицу (BookAuthors), где одна строка соответствует одному автору конкретной книги, а из таблицы книг убираем любое упоминание об авторах.

1. Books (Книги)
- title (название)
- isbn (ISBN-код)
- publicationyear (год издания)
- pagescount (количество страниц)
- availabilitystatus (статус доступности)
- location (местоположение/полка)

2. BookAuthors (Авторы книг)
- author (автор книги)
- book (книга)

3. BookGenres
– genre (жанр книги)
– book (книга)

Остальные таблицы не содержат повторяющихся групп, поэтому не подлежат исправлению.

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

Вторая нормальная форма (2NF): Устранение частичной зависимости атрибутов от первичного ключа.
2NF требует, чтобы все неключевые атрибуты полностью зависели от полного первичного ключа (первичные ключи могут быть составными). Нарушение возникает, когда есть зависимость неключевого атрибута лишь от части первичного ключа.
В этой части у нашей модели данных отсутствуют нарушения, поэтому для иллюстрации возьмем искуственный пример. Представим, что мы решили хранить книги со следующими атрибутами:

- BookTitle – название книги (ключ)
- DepartmentName – название отдела, где хранитс книга (история, художественная, компьютерная) (ключ)
- LocationShelf – номер стеллажа.

Атрибут LocationShelf зависит только от подразделения (DepartmentName), а от названия книги не зависит. Значит, нарушена 2NF, тк имеется частичная зависимость, а должна быть зависимость от обоих ключей.
Вопрос решается разделением одной таблицы на две:

📓Books
- BookId
- Title
- DepartmentId

🏢 Departments
- DepartmentId
- Name
- LocationShelf


☂️ Третья нормальная форма (3NF) ☂️
Исключение транзитивной зависимости атрибутов.

3NF требует отсутствия транзитивных зависимостей среди неключевых атрибутов. То есть, любой неключевой атрибут не должен зависеть от другого неключевого атрибута, кроме первичного ключа.
Всвязи с тем, что наши данные построены корректно с точки зрения 3 формы, возьмем пример:
Предположим, что информация о книге реализована так:
- ISBN – идентификатор книги
- Title – название книги
- Author – автор книги
- Reader - читатель

❓Проблемы данной структуры:

🔸 Ошибочная зависимость. Поле Reader зависит не только от уникального идентификатора книги (ISBN), но также косвенно от имени автора и названия книги. Это нарушение правил Третьей Нормальной Формы (3NF), так как возникают потенциальные аномалии модификации данных.

🔸 Избыточность данных. Повторяется одно и то же название книги и автор всякий раз, когда новый читатель берет книгу.

Для решения мы отделяем книг и читателей и создаем соединяющую таблицу «Выдача книг», как мы и сделали изначально.

На этом мы завершаем приведение к нормальным формам и переходим к физическому проектированию

🎲 Что делать на физическом проектировании? 🎲
В отличие от логического и концептуального уровней, физический уровень проектирования многоступенчатый и содержит несколько этапов внутри себя. Сначала мы перечислим основные из них, а затем будем смотреть, что из этого применимо к нашей «бибилиотечной» задаче

Шаги перехода к физическому проектированию:

✏️ Выбор СУБД: Определяемся с типом базы данных, которую будете использовать (MySQL, PostgreSQL, MongoDB и др.).

✏️ Определение структуры таблиц
- Какие поля будут присутствовать в каждой таблице?
- Каковы типы данных полей (строки, целые числа, даты)?

✏️ Настройка первичных ключей
- Каждое отношение должно иметь уникальный идентификатор (первичный ключ).

✏️ Создание внешних ключей
Нужно реализовать внешние ключи, обеспечивающие целостность данных. Внешний ключ ссылается на первичный ключ другой таблицы. Например, в таблице author_book внешний ключ ссылается на id автора (author_id) и id книги (book_id).

✏️ Индексация
Разобраться, есть ли необходимость в индексации и Определить индексированные поля , особенно те, по которым будут проводиться частые выборки. Индексы ускоряют выполнение SQL-запросов, связанных с поиском записей по значению ключа.

✏️ Обеспечение целостности данных:
Разобраться в необходимости использования триггеров и хранимых процедур для поддержания бизнес-правил и проверки данных на входе. Например, запрет на выдачу книги, если пользователь имеет штрафы.

✏️ Ограничения уровня БД
Добавить необходимые ограничения на уровне схемы (ограничения NOT NULL, UNIQUE, CHECK-контракты и другие правила, определяющие допустимые значения).

✏️ Оптимизация производительности
Решить, какие методы оптимизаций потребуются: разделение таблиц, горизонтальное масштабирование, репликация, шардинг и тд, в зависимости от предполагаемых нагрузок.

✏️ Шифрование чувствительной информации:
Если база данных содержит персональные данные читателей или иную конфиденциальную информацию, важно предусмотреть механизмы шифрования (либо на уровне самой базы данных, либо на уровне приложения).

📍Чекл-лист ключевых решений на этапе физического проектирования:

🟢 Создание физических схем данных (таблиц, индексов, представлений);
🟢 Настройка правил целостности и каскадных действий (ON DELETE CASCADE, ON UPDATE CASCADE);
🟢 Организация резервного копирования и восстановления данных;
🟢 Установка политик паролей и механизмов аутентификации пользователей;
🟢 Планирование инфраструктуры (расположение сервера, сетевые настройки, безопасность доступа);
🟢 Конфигурация базы данных для повышения производительности (параметры буферного кеша, временные зоны, настройка памяти).

🔑 О первичных ключах 🔑

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

Есть понятие «Естественная идентификация». Например, каждая книга однозначно идентифицируется по своему ISBN-коду. Но есть сущности, которые не имеют своей собственной идентификации. Например, таблицы со связями. То есть если таблица характеризует какую-то связь между сущностями, то в ней может не быть первичного ключа вообще.

🔐 А что про составной ключ?

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

📌 Пример:
Регистрация сотрудников на курсы повышения квалификации (employee_courses), где один сотрудник не может дважды записаться на один и тот же курс в одно и то же время (employee_id, course_id, start_date).

🗑 Когда избегать составного ключа

🗳 Дублирование значений
Каждый раз, когда добавляем новую запись, приходится повторять одни и те же значения. Это увеличивает объем данных и снижает эффективность хранения.

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

🗳 Производительность при индексации
Если количество индексов большое, база данных тратит больше ресурсов на обслуживание индексов, замедляя работу системы.


Как считаете, в нашей задаче есть сущности, для которых подойдет составной первичный ключ?

🩷 Отвлекусь всего лишь на мгновение...

Обычно я стараюсь держаться подальше от личного в нашем профессиональном пространстве, но сегодня захотелось сделать исключение.

Вы знаете, что недавно выступила на конференции ЛАФ. Для многих это событие проходит незаметно, но для меня оно стало особенным — моим первым опытом выступления офлайн перед настоящими профессионалами своего дела. Это было непросто: надо было учесть разные уровни подготовки аудитории, объяснить сложные вещи простым языком, дополнить всё живым примером и укладываться строго в отведённое время. Ведь профессионалы — самые требовательные судьи.

Сегодня организаторы передали мне отзывы... От вас, дорогие слушатели. Вы оценили мою работу высоко! Значит, мои советы действительно пригодились вам и попали в цель. А это дорогого стоит.

Это выступление имело огромное значение лично для меня, потому что именно от него зависела моя дальнейшая судьба в мире конференций. Когда увидела вашу положительную реакцию, поняла, что нашла свою нишу и должна двигаться вперёд.

Огромная благодарность моему супер-куратору Саше и ПСБ Банку, благодаря которым я смогла реализовать свою идею!

Теперь я уверена, что мы можем достичь большего вместе! Спасибо каждому из вас за поддержку и доверие!

📜 Физическая модель данных 📜
На данном этапе разрабатывается физическая структура базы данных, учитывающая особенности выбранной СУБД. Определяются типы полей, индексы, ограничения целостности, триггеры и другие элементы физической структуры.

Структура

🪧Книги
Обратите внимание на типы данных title ISBN. в чем разница?
Название книги - поле переменной длины. ISBN - фиксированной длины. Если символов меньше 13, они заполняются пробелами

Books (
  id INT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(255),
  isbn CHAR(13),
  publicationyear YEAR,
  pagescount INT,
  availabilitystatus ENUM('Available', 'Borrowed', ‘Borrowed_here’),
  location VARCHAR(255)
)   

🪧 Соединение автора и книги
Таблица состоит полностью из внешних ключей

BookAuthors (
  book_id INT,
  author_id INT,
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (author_id) REFERENCES Authors(id)
)

🪧 Соединение жанра и книги

BookGenres (
  book_id INT NOT NULL,
  genre_id INT NOT NULL,
  PRIMARY KEY(book_id, genre_id),
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (genre_id) REFERENCES Genres(id)
)

🪧 Авторы

Authors (
  id INT PRIMARY KEY AUTO_INCREMENT,
  fullname VARCHAR(255),
  biography TEXT
)

🪧 Жанры книг

Genres (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)

🪧 Отдел хранения книг

Departments (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255)
)

Клиенты

Clients (
  id INT PRIMARY KEY AUTO_INCREMENT,
  firstname VARCHAR(255),
  lastname VARCHAR(255),
  readerticketnumber VARCHAR(255),
  phonenumber VARCHAR(255),
  email VARCHAR(255),
  address TEXT
)

🪧Выдача книг
А вот и составной первичный ключ. У записи не делаем свой идентификатор, а делаем первичный ключ из книги и даты выдачи. Одну книгу нельзя выдать 2 раза в один момент. Однозначная идентификация книги обеспечена

Loans (
  book_id INT,
  issuedate DATE,
  user_id INT,
  returndate DATE,
  actualreturndate DATE NULLABLE,
  loantype ENUM('ReadingRoom', 'HomeLoan'),
  PRIMARY KEY(book_id, issuedate),
  FOREIGN KEY (book_id) REFERENCES Books(id),
  FOREIGN KEY (user_id) REFERENCES Users(id)
)

🪧 Штрафы

Fines (
  id INT PRIMARY KEY AUTO_INCREMENT,
  loan_id INT,
  amount DECIMAL(10,2),
  reason TEXT,
  status ENUM('Paid', 'Unpaid'),
  FOREIGN KEY (loan_id) REFERENCES Loans(id)
)

🧮 Индексация 🧮
Следующим пунктом в нашем списке по физическому проектированию идет индексация. Для начала разберемся с этим понятием.

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

🖍Когда нужна индексация?

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

📥 Частые запросы
Если часто выполняются запросы на чтение данных (например, SELECT-запросы), особенно если они фильтруют большие объемы данных.

📥 Запросы с условиями
Запросы, содержащие условия WHERE, ORDER BY, GROUP BY и JOIN, значительно ускоряются благодаря наличию соответствующих индексов.
📥 Операции чтения важнее операций записи
Индексы замедляют операции вставки, обновления и удаления данных, поскольку каждый раз нужно обновлять сами индексы. Поэтому индексация выгодна там, где преобладают операции чтения над операциями изменения данных.

🔎 Какие бывают индексы?

Существует несколько типов индексов, каждый из которых оптимизирован под разные сценарии использования

🔗 B-Tree (B-дерево)

Самый распространенный вид индекса. Используется в большинстве реляционных СУБД (PostgreSQL, MySQL, SQLite). B-Tree позволяет эффективно искать значения по диапазону, сортировать и объединять таблицы.

Преимущества
Эффективная поддержка операторов сравнения (<, >, =).
Позволяет сортировку результатов по заданному полю.
Недостатки
Больший объем памяти для хранения больших объемов данных.


🔗 Hash-индексы

Hash-индексы используются, когда важно исключительно точное совпадение значений (оператор равенства "="). Они быстрее работают с поиском конкретного значения, но не поддерживают диапазон запросов или сортировки.

Преимущества
Очень быстрый доступ по ключу (равенство).
Недостатки
Невозможность эффективного использования с операторами "<", ">", BETWEEN и подобными.


🔗 Bitmap-индексы

Используются в основном для столбцов с небольшим числом уникальных значений ("низкой селективностью"). Например, такие поля, как пол, статус заказа, страна проживания. Отличаются компактностью и скоростью обработки логических условий.

Преимущества
Компактность и высокая производительность для низкоселективных полей.
Недостатки
Медленно работает с высокоселективными данными.


🔗 Full-text-индексы

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

Преимущества
Поддержка сложных операций поиска по словам и фразам.
Недостатки
Сложнее поддерживать и занимают больше места.





❓Как принимать решение о необходимости индексирования?

*️⃣ Типичные запросы
Проведите профилирование ваших приложений и проанализируйте наиболее частые типы запросов. Индексировать имеет смысл именно те поля, которые участвуют в условиях WHERE, ORDER BY, GROUP BY и JOIN.


*️⃣ Анализ производительности
Используйте инструменты анализа запросов вашей базы данных (EXPLAIN ANALYZE в PostgreSQL, EXPLAIN в MySQL и др.) для оценки эффективности существующих планов выполнения запросов. Если запрос долго выполняется, возможно, дело в отсутствии нужного индекса.


*️⃣ Объем данных
Чем больше таблица, тем сильнее ощущается эффект от добавления индекса. Для небольших таблиц индексирование может оказаться избыточным и неоптимальным решением.


*️⃣ Частота изменений
Если ваши данные часто меняются (INSERT/UPDATE/DELETE), убедитесь, что выгода от индекса превышает затраты на обновление самого индекса.


Критерии выбора типа индекса
Важно правильно выбрать тип индекса исходя из особенностей вашего приложения и структуры данных. Например, если важна скорость точного поиска, выбирайте hash-индекс, если важны диапазонные запросы — b-tree, если нужны быстрые операции по низким селективностям — bitmap.

🔔 Критерии оценки потребности в индексе

♦️Затраты на поддержку индекса
Оценивайте нагрузку на систему от поддержания индекса. Чрезмерное количество индексов приведет к увеличению нагрузки на сервер при операциях INSERT/UPDATE/DELETE.


♦️ Выбор подходящего типа индекса
Убедитесь, что выбран правильный тип индекса, соответствующий вашим требованиям и типу запросов.


♦️ Оценка селективности
Селективность — доля строк, удовлетворяющих условию фильтра. Низкая селективность предполагает создание специфичных индексов вроде bitmap.


♦️ Производительность системы
Анализируйте метрики производительности (время отклика, использование CPU, RAM и I/O), чтобы оценить реальную пользу от новых индексов.


♦️ Тестирование на реальных нагрузках
Всегда проверяйте эффективность индексов на реальных рабочих сценариях, используя реплики продакшн-данных.

Таким образом, правильная стратегия индексирования должна балансировать между производительностью чтения и затратами на обслуживание индексов.

❓Много столбцов или много таблиц с отношением 1-to-1

Меня давно волнует этот вопрос. И вот в очередной раз, когда я им задалась, я решила поставить точку и докопаться до истины. К чему я пришла, сейчас расскажу.

🌀 Один большой набор столбцов в одной таблице

➕Плюсы
1. Простота модели: Всё находится в одном месте, проще понимать структуру данных и писать запросы.
2. Быстрота исполнения SELECT'ов: Поскольку вся необходимая информация хранится в одной таблице, не нужно соединять таблицы (JOIN), что повышает скорость выборок.
3. Отсутствие дополнительного уровня сложности: Меньше технических деталей вроде внешнего ключа и ограничения целостности, меньше шансов допустить ошибку.

➖ Минусы
1. Проблемы с пустыми значениями: Если большинство столбцов редко используются (они часто пусты), увеличивается размер строки и неэффективность хранения данных.
2. Ухудшение производительности UPDATE и INSERT: Операции изменения данных становятся тяжелее, поскольку затрагивается больше столбцов даже при изменении одного значения.
3. Потеря контроля над целостностью данных: Нет чёткого разделения между обязательной информацией и дополнительными деталями.

🔱 Несколько связанных таблиц с отношением one-to-one

➕ Плюсы
1. Модульность и разделение обязанностей: Структура становится чище и понятнее, легко добавлять новые сущности или удалять ненужные.
2. Эффективность хранения: Вы можете сохранить минималистичный дизайн главной таблицы, уменьшив объём памяти для хранения редко используемых данных.
3. Контроль целостности: Легче контролировать наличие обязательных данных и предотвращать ввод лишней информации.
4. Производительность при изменениях: Изменяя одну таблицу, мы не трогаем другие, что ускоряет операции обновления и вставки данных.

➖ Минусы
1. Необходимость JOIN'ов: Каждый раз, когда нужно собрать всю информацию вместе, приходится объединять таблицы, что немного усложняет запросы и может снизить производительность.
2. Дополнительная сложность модели: Больше таблиц и внешних ключей увеличивают сложность схемы и повышают вероятность ошибок при разработке.

⁉️Так что в итоге? Когда что использовать?

Один большой набор столбцов
Подход хорош, если:
- Таблица маленькая и проста в управлении.
- Все столбцы активно используются.
- Требуется максимальная простота реализации и минимальное число соединений.
🆚
Несколько связанных таблиц (отношения one-to-one)
Лучше применять, если:
- Часть данных используется редко или вовсе необязательна.
- Есть необходимость чётко разделять разные виды данных (например, персональные данные и служебные характеристики).
- Нужно обеспечить высокий уровень производительности при изменениях данных.

После разбора индексов можно переходить к корректному обеспечению бизнес-логики. Для этого есть 2 типа методов в СУБД, шпаргалку с которыми я представила ниже.

🖍 Триггер
Триггер — это специальная программа или набор инструкций, автоматически выполняемых базой данных при наступлении определённого события. Например, такие события включают вставку новой записи (INSERT), обновление существующей записи (UPDATE) или удаление записи (DELETE).

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

🖍 Хранимая процедура
Хранимая процедура — это заранее подготовленный блок SQL-кода, хранящийся внутри базы данных и доступный для многократного вызова различными приложениями. Процедуры часто используются для реализации сложных бизнес-правил, обработки транзакций или улучшения производительности путем оптимизации запросов.

Примеры случаев использования хранимых процедур
1. Выполнение сложной логики обработки данных: расчет зарплаты сотрудников с учётом премий, налогов и прочих факторов.
2. Оптимизация производительности путём объединения нескольких операций в одну процедуру: выполнение множества действий одновременно (например, массовое обновление/добавление данных).

Ниже наглядная таблица, иллюстрирующая разницу между этими понятиями

❓❓Почему нужно использовать хранимые процедуры вместо обычных селектов?

Я часто слышу о необходимости оборачивания бизнес-логики, реализуемой на стороне БД, в ХП. На вопрос "почему" обычно получаю краткое "безопасно".
Разберемся, в чем заключается эта безопасность и в чем тут реально дело.



🪭 И правда, безопасность

• Минимизация SQL-инъекций: Хранимые процедуры используют параметризованные запросы, что снижает риск SQL-инъекций.
• Контроль доступа: Можно ограничить доступ к таблицам напрямую, предоставив права только на выполнение хранимок.
• Аудит действий: Легче отслеживать, кто и какие операции выполнял, если все изменения идут через процедуры.

🪭 Производительность

• Предварительная компиляция: хранимки компилируются и оптимизируются при создании, что ускоряет выполнение.
• Снижение сетевого трафика: Вместо отправки больших SQL-запросов клиент передает только имя процедуры и параметры.
• Локальная обработка: Сложные операции выполняются на сервере, а не на клиенте, что уменьшает нагрузку на приложение.

🪭 Упрощение поддержки

• Централизованная логика: Изменения в бизнес-логике вносятся в одном месте - в хранимке, а не во всех клиентских приложениях.
• Согласованность данных: Все приложения используют одни и те же процедуры, что уменьшает риск ошибок из-за разных реализаций.

🪭 Контроль за транзакциями

• Упрощение управления транзакциями: Можно объединять несколько операций в одну транзакцию внутри хранимки, гарантируя атомарность.
• Автоматический откат при ошибках: Если в процедуре возникает ошибка, можно откатить изменения без дополнительного кода на клиенте.

🪭 Масштабируемость

• Разгрузка приложения: Сервер БД берет на себя часть вычислительной нагрузки.
• Возможность кеширования планов запросов: SP могут использовать кешированные планы выполнения, что ускоряет повторные вызовы.

🗝 Когда хранимые процедуры могут быть избыточны?

• В простых CRUD-приложениях без сложной бизнес-логики.
• В системах, где важна гибкость и быстрая итерация (например, NoSQL или ORM-подход).
• В микросервисных архитектурах, где бизнес-логика вынесена в сервисы.

🔝 Топ-7 мифов и заблуждений при проектировании БД на физическом уровне

Рассмотрим самые распространенные ошибки, которые совершаются на этапе физического проектирования.

Миф №1: Нормализованная база данных — это лучшая практика всегда

📛 Часто считается, что нормализацию нужно проводить всегда и обязательно стремиться к третьей нормальной форме (3NF). Однако чрезмерная нормализованность может привести к увеличению числа соединений (JOIN), замедлению выполнения запросов и усложнению структуры базы данных.

✅ Необходимо учитывать требования конкретной предметной области и балансировать между производительностью и целостностью данных. Иногда денормализация вполне оправдана, особенно в высоконагруженных системах или системах OLAP (аналитические системы).

Миф №2: Индексация решит любые проблемы производительности

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

✅ Анализируйте нагрузки на систему и создавайте индексы осознанно, исходя из реальных сценариев использования. Используйте подходы профилирования запросов и анализа медленных запросов.

Миф №3: Физический дизайн определяется исключительно моделью данных

📛 Некоторые считают, что физический уровень зависит только от логической модели данных. Хотя логическая структура важна, физическое проектирование должно также учитывать особенности используемого оборудования, платформы и программного обеспечения.

✅ Учитывать аппаратные ресурсы (количество ядер CPU, объём оперативной памяти, ёмкость дисков), выбрать подходящие механизмы управления памятью и I/O, настроить параметры резервного копирования и восстановления.

Миф №4: Размер таблицы не имеет значения

📛 Многие полагают, что размер таблицы не влияет на производительность запросов и общие характеристики системы. Большие таблицы могут стать причиной деградации производительности и затруднений в обслуживании.

✅ Использовать методы горизонтального шардинга (разбиения больших таблиц на части), кластеризации данных и продуманного подхода к индексированию.

Миф №5: Оптимизация базы данных начинается после завершения разработки

📛 Оптимизация и настройка базы данных откладываются на финальную стадию проекта. В результате многие проблемы обнаруживаются поздно, и исправлять их становится сложнее и дороже.

✅ Регулярно тестировать и анализировать поведение системы, заниматься настройкой производительности на ранних этапах жизненного цикла проекта.

Миф №6: Отказоустойчивость достигается одним методом

📛 Существуют универсальные рецепты отказоустойчивости, такие как репликация или резервное копирование. На самом деле разные сценарии требуют разных подходов и комбинаций методов.

✅ Применяйте комплексный подход к обеспечению отказоустойчивости, включающий репликацию, зеркалирование, архивацию, регулярное тестирование аварийного восстановления и мониторинг состояния системы.

Миф №7: Высокий уровень абстракции скрывает физические ограничения

📛 Современные инструменты ORM (Object Relational Mapping) позволяют игнорировать физическую структуру базы данных и сосредоточиться только на объектной модели. Однако физическая реализация оказывает значительное влияние на производительность и эффективность запросов.

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

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


🌐 Первичный ключ (PRIMARY KEY)

• Что делает: Уникально идентифицирует каждую запись в таблице.
• Зачем нужно:
– Гарантирует уникальность (не может быть дубликатов).
– Обеспечивает быстрый доступ к данным (индексируется).
– Используется для связей между таблицами (FOREIGN KEY).

🌐 Внешний ключ (FOREIGN KEY)

• Что делает: Связывает поле одной таблицы с PRIMARY KEY другой таблицы.
• Зачем нужно:
– Поддерживает ссылочную целостность (нельзя ссылаться на несуществующие записи).
– Автоматически удаляет/обновляет связанные записи (если задано ON DELETE CASCADE или ON UPDATE CASCADE).

🌐 Уникальность (UNIQUE)

• Что делает: Гарантирует, что значения в столбце (или комбинации столбцов) не повторяются.
• Зачем нужно:
– Позволяет избежать дублирования (например, email пользователя).
– Отличается от PRIMARY KEY тем, что может быть NULL (в некоторых СУБД).

🌐 Проверка (CHECK)

• Что делает: Ограничивает допустимые значения в столбце по условию.
• Зачем нужно:
– Обеспечивает бизнес-правила (например, возраст > 0, дата окончания > даты начала).
– Пример:

🌐 Непустое значение (NOT NULL)

• Что делает: Запрещает NULL в столбце.
• Зачем нужно:
– Гарантирует, что критически важные данные (например, user_id) всегда заполнены.
– Уменьшает ошибки при обработке данных.

🌐 Значение по умолчанию (DEFAULT)

• Что делает: Устанавливает значение по умолчанию, если оно не указано при вставке.
• Зачем нужно:
– Упрощает вставку данных (например, created_at DEFAULT CURRENT_TIMESTAMP).
– Позволяет избежать NULL, если значение не задано.


🌐 Условия на уровне таблицы (CONSTRAINT)
сложные CHECK на несколько столбцов.

Зачем вообще нужны ограничения?

1. Целостность данных – защита от некорректных или противоречивых данных.
2. Безопасность – предотвращение случайных или злонамеренных изменений.
3. Производительность – индексы (PRIMARY KEY, UNIQUE) ускоряют запросы.
4. Согласованность – гарантия, что связи между таблицами (FOREIGN KEY) всегда корректны.

Котики!
Не теряемся! Я пока под завалами своих проектов, и нет времени писать в канал. Скоро всё раскидаю и вернусь.
Коротко о том, что хотят мои заказчики, показала на картинке 😆
причем так происходит везде.