Создание базы данных устройства. Лучше ли иметь все атрибуты устройства в одной таблице или во многих отдельных таблицах?
Я создаю базу данных для мониторинга устройств, и я зашел в тупик.
У меня есть 40 различных типов устройств, каждое из которых записывает различную информацию и имеет некоторые общие (например, имя устройства, дата выборки), но в основном отличающиеся атрибуты (например, signal_50_100Hz, device_arm_attached).
На мой взгляд, есть два варианта:
Создайте большую таблицу, которая включает и исчерпывает все атрибуты со всех устройств.
- Минусы: не все устройства будут иметь значения для всех атрибутов, и не все атрибуты будут актуальны для всех устройств. Большая часть таблицы может оказаться пустой, и мне, возможно, придется создать таблицу для записи имен атрибутов для каждого устройства, чтобы получить осмысленный вывод.
- Плюсы: наличие одной таблицы значительно упрощает и упрощает агрегацию и свертывание значений позже, требует меньше SQL для написания, более обобщенного и простого для понимания.
Создайте отдельную таблицу для каждого типа устройства, перечислив каждый атрибут отдельно в каждой таблице.
- Минусы: нужно писать пользовательский SQL каждый раз, когда я получаю новое устройство, перефразировать всю систему, нет общего интерфейса. Большое количество таблиц для создания и управления самостоятельно.
- Плюсы: все изложено кратко и может быть отделен, чтобы стоять в одиночестве, если это будет необходимо. Дополнительного багажа нет, и один тип устройства не связан с другими типами устройств через обобщенный набор атрибутов.
Цель этой базы данных состоит в том, чтобы постоянно объединять информацию с этих устройств мониторинга в единое представление, которое затем запрашивается в зависимости от потребностей аналитика.
Мне нравится идея создания разных таблиц для каждого устройства, а затем использовать общее представление для объединения соответствующих данных из этих таблиц в более удобную и общую форму. Это позволило бы мне сохранить данные (для сохранения) и агрегировать их по мере необходимости из общего абстрагированного слоя, одного большого представления.
Видите ли вы какие-либо проблемы с этим подходом? Какой подход имеет больше смысла? Какие еще стратегии я могу использовать? Есть ли у кого-нибудь общие советы о том, как лучше подойти к этому вопросу?
2 ответа
Это старая проблема полиморфизма в реляционных базах данных. Есть несколько шаблонов для рассмотрения.
Одна таблица с объединением всех столбцов для всех устройств. Вид отстой, потому что будет много пустых значений для столбцов, которые не связаны, поэтому невозможно добавить ограничения NOT NULL, которые должны применяться к некоторым устройствам. Кроме того, вы должны изменить схему при добавлении нового устройства, которое требует новых столбцов.
Одна таблица для каждого устройства, а затем объединить их вместе для просмотра запросов по всем устройствам. Вид отстой, потому что вы должны добавить новую таблицу и изменить представление для новых устройств.
Шаблон EAV. Вид отстой, потому что это влияет на производительность запросов и невозможно добавить ограничения NOT NULL, которые должны применяться к некоторым устройствам.
Одна таблица со столбцом JSON для атрибутов, которые различаются для разных устройств. Это приближается к естественному представлению ваших данных, но это отстой, потому что вам приходится управлять любыми индексами значений. https://dev.mysql.com/doc/refman/5.7/en/create-table-secondary-indexes.html
В большинстве случаев, если вы добавляете новое устройство и оно имеет новые атрибуты, и вы намерены, чтобы приложение каким-то образом использовало эти атрибуты, вам придется писать код. Нет способа избежать этого. Даже если у вас есть гибкая структура таблиц, которая может содержать что-либо, нет смысла что-либо удерживать, если вы что-то не делаете с этим, и программное обеспечение, которое что-то делает с этим, нуждается в модификации для того, чтобы что-то делать. Так что идея "разъединения" и того, что больше ничего не нужно менять, - это просто абстрактная мечта.
Если вы планируете хранить данные, но не планируете вообще что-либо с ними делать (хранилище для хранения, например, в целях обеспечения слышимости или соблюдения требований), вы можете хранить все это в гибком поле (скажем, в большом varchar).) как структура XML или JSON. Это было бы совершенно гибким.
В любом случае вы хотели бы начать с одной таблицы. Таблица может содержать уникальный первичный ключ для каждого устройства, имя или серийный номер, номер модели, а затем ряд столбцов, которые являются общими или полезными для всех устройств. Если атрибут является общим, но существует, скажем, только на 90% ваших устройств, было бы хорошо создать столбец для атрибута и оставить его пустым, если он не применяется. Если атрибут или набор атрибутов принадлежит только небольшому подмножеству устройств, я хотел бы разбить их на отдельные таблицы или таблицы, но с тем же первичным ключом. Чтобы защитить свой код от изменений схемы, вы можете создать представление, которое будет полезным образом объединять таблицы и использовать это представление в качестве основы для хранимых процедур или операторов выбора.