Сравнение вариантов

Краткое сравнение

1. Хранение событий

Старая схема:

• 5+ таблиц (event_types, event_repairs, event_diagnostics, event_fracas, event_movements).
• Для добавления нового типа события нужно создавать новую таблицу.
• Для добавления новых полей в существующие события, нужно менять структуру таблиц.

Новая схема:

• 2 таблицы (event_types, module_events).
• Для добавления нового типа события нужно добавить строку в event_types и определить формат JSON для этого события.
• Для добавления нового поля в событие - просто изменить формат JSON.

В чем разница:

Если нужно добавить тип события "Климатические испытания", то в старой схеме нужно создавать новую таблицу CREATE TABLE event_climatics.

Если нужно добавить поле "Влажность", в старой схеме нужно изменять таблицу ALTER TABLE event_diagnostics ADD COLUMN humidity.

В новой схеме - в обоих случаях работа с JSON, без перестроения структуры БД.

Т. к. таблица module_events одна, то достаточно выполнить SELECT * FROM module_events для получения всех событий - не нужно джойнить 5+ таблиц, база работает быстрее.

Примеры:

1. Событие типа movement может быть описано как:

{
  "from_location_id": 1,
  "to_location_id": 2,
  "comment": "Отправлен на модульный участок"
}

2. Событие типа diagnostic так:

{
  "diagnostic_location_id": 2,
  "result": "Неисправен",
  "faulty_nodes": [3, 5],
  "comment": "Первые два узла в норме"
}

3. Новое событие типа climatic, например, так:

{
  "version": 1,
  "temperature_min": -40,
  "temperature_max": 85,
  "humidity_percent": 95,
  "cycles": 10,
  "result": "Успешно"
}

2. Хранение компонентов

Старая схема:

• Общая таблица components.
• Отдельные таблицы под типы passive_components, diodes, transistors и т. д.
• Таблицы-справочники packages, manufactures.
• Для добавления нового типа компонента нужно создавать новую таблицу.
• Для добавления нового компонента (например, транзистора) нужно создать запись в components, а затем записать его характеристики в таблицу transistors.

Новая схема:

• Одна, но гибкая таблица components.
• Для добавления нового компонента (или нового типа компонента) нужно добавить строку в components и его описание в JSON-поле specs.

В чем разница:

Чтобы вывести список компонентов, по старой схеме нужно делать сложные LEFT JOIN ко всем таблицам типов компонентов.

В новой схеме — простой SELECT * FROM components.

Примеры:

{
  "type": "resistor",
  "size": "0603",
  "resistance": "1 кОм",
  "power": "0.125 Вт",
  "tolerance": "1%"
}

или

{
  "type": "resistor",
  "size": "0603",
  "resistance_ohm": 1000,
  "power_w": 0.125,
  "tolerance": "1%"
}

С полями JSON можно работать как с виртуальными колонками. Например, если нужно посчитать количество всех резисторов в БД, то можно сделать так:

ALTER TABLE components
ADD COLUMN component_type VARCHAR(32)
    AS (JSON_UNQUOTE(JSON_EXTRACT(attributes, '$.type')))
    VIRTUAL;

SELECT COUNT(*) AS cnt_resistors
FROM components
WHERE component_type = 'resistor';

Аналогично можно поступить с полями в JSON вроде ref_des, package и пр., если будет необходимо.

3. Иерархия Тип модуля→Узел модуля→Компонент

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

Старая схема:

• Неправильно названная таблица module_nodes - на деле это просто nodes, наименования узлов без привязки к типу модуля.
• Неправильно названная таблица с компонентами module_components - должно быть module_type_components, т. к. есть ссылка на module_type_id.
• Таблица с компонентами module_components построена по принципу «всё в кучу».

Новая схема:

• Таблица module_type_nodes содержит названия узлов с привязкой к типу модуля (см. выше пример с faulty_nodes).
• Таблица module_type_node_components содержит компоненты с привязкой к узлу.

В чем разница:

В старой схеме узлы – это просто словарь наименований. Связь с типом модуля существовала только в момент привязки компонента.

В новой схеме нельзя создать узел, не указав, к какому типу модуля он принадлежит.

Примеры:

В старой схеме узел не привязан к типу модуля, поэтому в таблице module_nodes может быть, например, узел с наименованием Узел экстрактора. Чей это узел? Неизвестно. Чтобы узнать, надо обратиться к таблице module_components. Это порождает сразу два плохих сценария:

• Переиспользование (можно использовать один узел в нескольких модулях, но если узел в будущем захотят переименовать в одном типе модуля, в других типах модулей это наименование может стать некорректным).
• Дублирование без связи (можно создать несколько узлов с одинаковым названием и помнить, что id=1 относится к одному типу, а id=2 к другому типу, но со временем запутаться и допустить ошибку, сославшись не на тот id - проверки есть ли такой узел у типа модуля нет).

Если таблица построена по принципу «всё в кучу», то в старой схеме будет следующее что-то вроде: В типе модуля ... есть ... резистор, который относится к узлу ....

В новой схеме В узле ... есть ... резистор, при этом тип модуля вообще не нужен, т. к. он известен через узел.

4. Упрощение справочников

Старая схема:

• Таблица-справочник module_statuses.
• Таблица модулей modules со ссылкой на module_statuses.

Новая схема:

• Одна таблица modules.

В чем разница:

Снижение «бюрократии» в базе данных. Читаемость и скорость. Разработчик сразу видит статус модуля без лишних подзапросов - в виде строки OK, FAULTY, TECH.

Примеры:

В старой схеме - SELECT m.serial_number, s.name FROM modules m JOIN module_statuses s ON ....

В новой схеме - SELECT serial_number, status FROM modules.

5. Производительность и масштабируемость

Старая схема:

• Данные сильно раздроблены. Чтобы показать «Карточку модуля», нужно собрать данные минимум из 5+ таблиц.

Новая схема:

• Все ключевые данные лежат под рукой.

В чем разница: Такие системы учёта читают (просматривают) в 100 раз чаще, чем в них пишут данные.

Примеры: Поле last_module_event_id прямо в таблице modules позволяет вывести список модулей с их последним статусом и датой изменения мгновенно, без сканирования всей тяжелой таблицы событий.

Минусы новой схемы

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

   В новой схеме также существуют жесткие связи (module_id, user_id и пр.), но детали внутри JSON - мягкие. База не проверяет, что внутри JSON валидное местоположение или валидный номер узла. Поэтому ответственность переносится на код приложения (в вебе - данные валидируются сначала на frontend, а затем и на backend).

2. Использование ENUM для статусов модуля оправданно, если статусов мало, их не нужно добавлять или менять их порядок через GUI.

   Если через какое-то время нужно будет добавить новый статус модуля, например, UNRECOVERABLE, то это вызовет тяжелый ALTER TABLE, который на какое-то время заблокирует таблицу modules. При этом даже это зависит от версии MySQL: например, в версии 5.7 блокировка неминуема, а в версии 8.0+ если статус добавляется в конец, то блокировки не будет - блокировка будет только, если статус нужно будет добавить в середину ENUM.