Какую структуру таблиц выбрать для описания некоторой сущности, у представителей которой часть атрибутов совпадает, а часть — различна?
Всем добрый день! Проектирую структуру базы данных в MySQL, и возникла проблема: предположим, что есть некая сущность, которую нужно отразить в таблицах базы данных. У всех конкретных экземпляров этой сущности есть некий общий набор свойств, но есть свойства, которые различаются для некоторых подмножеств экземпляров сущности.
Например, у продаваемых машин (новых или бывших в употреблении) есть некий общий набор параметров (производитель, цвет, год выпуска и т.д.). Однако если машина новая, то у неё есть особые параметры, - например, связанные с гарантией от производителя, чего нет у подержанной машины. А у подержанных машин есть свойства, которых нет у новых: пробег, длительность эксплуатации и т.д.
Как быть в этом случае? Создавать единую таблицу с кучей null или же несколько раздельных таблиц? Или делать таблицу для общих свойств и вспомогательные таблицы для дополнительных свойств? Может, есть некая общепринятая практика в этом случае?
Дополнительно:
Путей много. Можно завести 2 таблички. Одна для новых машин. Другая - для подержанных. Со
своими наборами пропертей. Тогда и индексы строить удобно.
И с точки зрения типизации этот подход верный. Если язык разработки (Python/PHP) различает
типы машин - то для каждого типа нужна отдельная табличка. Это в духе ORM.
Недостаток - надо делать union all двух таблиц если мы хотим делать поиск по общим пропертям.
Можно завести 1 табличку с полем типа JSON и свалить туда все проперти которые могут быть
опциональны для новых машин и для Б/У. Это делает схему более компактной. И поиск по основным
полям работает универсально. Для кастомных полей надо искать описание в MySQL языков работающих
с JSON (JSonPath) для того чтоб выбирать и фильтровать и индексировать их.
Можно поступить как в BigData. Свалить все проперти что есть в одну большую таблицу. Будет в ней
допустим 500 колонок. И большая часть из них - пустая. Заполняется null. Такая модель тоже работоспособна.
Но для человека наблюдающего глазами таблицу будет неудобно с ней работать. Особенно когда нужное
тебе поле находится где-то на 400х колонках и надо скроллить грид мышкой вправо чтоб хотя-бы прочитать
глазами значения. И эволюция такой схемы проходит тяжелее. Т.к. alter table обычно блокирует таблицу
от транзакций DML и нужен регламент что добавлять новую колонку.
Если у вас используется ORM, то спихните всю работу на него. Он вам через правильное наследование классов и подход code first сам сгенерирует нужную ему структуру в БД (вроде будет генерироваться одна таблица на сущность с кучей столбцов, это если 1-1 связь. Если 1-М или М-М - то будут дополнительные таблицы).
Если сами руками все делаете в Базе Данных, то можете просто ввести дополнительный столбец под JSON и писать туда доп.параметры. Правда не знаю как там индексируется это поле и настраивается поиск по нему, но скорее всего современные СУБД такое умеют.
Ответы:
Как быть в этом случае? Создавать единую таблицу с кучей null или же несколько раздельных таблиц? Или делать таблицу для общих свойств и вспомогательные таблицы для дополнительных свойств? Может, есть некая общепринятая практика в этом случае?
Нет чёткой общепринятой практики, потому что в разных случаях оптимальное решение может быть разное, в зависимости от постановки задачи.
Иногда "единую таблицу с кучей null", иногда json, иногда EAV (не рекомендуется, но всё же лучше иметь возможность (знать о возможности), чем не иметь возможность): https://qna.habr.com/q/1224626
У каждого варианта свои минусы и плюсы.
Например, если "общих параметров" больше, чем "особых параметров", то куча null выглядит разумным выбором.
- Спасибо за ответ, буду рассматривать варианты, предложенные по указанной вами ссылке. Ситуация слегка усугубляется тем, что в моём случае "стандартных" экземпляров , грубо говоря, 4-5 тысяч, а "нестандартных", с дополнительными свойствами, - около сотни. Не хотелось бы вводить кучу лишних полей ради сотни записей. Возможно, формат json был бы неплохим вариантом, буду думать.
Как правильно написали выше, общепринятой практики нет.
С натяжкой общепринятой можно назвать практику, когда часть свойств пишется в JSON поле, но как раз для описанного в вопросе случая она не должна применяться. Здесь однозначно подходит стандартная реляционная структура. Нет гарантии? Пишем null. Нет пробега? Пишем 0 км.
А вот если нам надо хранить совсем различные товары, например автомобили и автомагнитолы, то специфические для товара характеристики можно писать в JSON поле. Но в этом случае потребуется отдельная база данных, которая будет хранить все возможные варианты ключей в этом JSON поле.
- Спасибо за ответ! В вопросе я привёл просто пример такой структуры. В случае json - вы имеете в виду именно отдельную базу или таблицу? Нет ли источников информации, где можно глянуть пример реализации такого подхода?
- Таблицу. Базу данных это я образно написал. Чтобы подчеркнуть тот факт, что нужна отдельная структура для хранения возможных свойств. С привязкой к категории товара.
Опишите проблему, и специалист поможет с настройкой, исправлением ошибки или доработкой сайта. Подберём понятный план работ без лишней переписки.
Пока нет других ответов. Будьте первым, кто поможет автору.
Ответить на вопрос
Для описания сущности, у представителей которой часть атрибутов совпадает, а часть различна, можно использовать таблицы с разделением атрибутов на общие и уникальные. В таком случае можно создать две таблицы: одну для общих атрибутов, которые имеются у всех представителей сущности, и вторую для уникальных атрибутов, которые отличаются для каждого представителя.
Пример структуры таблицы для описания такой сущности можно представить следующим образом:
CREATE TABLE common_attributes ( id INT PRIMARY KEY, common_attribute1 VARCHAR(255), common_attribute2 INT, common_attribute3 DATE ); CREATE TABLE unique_attributes ( id INT PRIMARY KEY, common_id INT, unique_attribute1 VARCHAR(255), unique_attribute2 DECIMAL(10, 2), unique_attribute3 BOOLEAN, FOREIGN KEY (common_id) REFERENCES common_attributes(id) );
В данном примере у нас есть две таблицы: common_attributes и unique_attributes. Первая таблица содержит общие атрибуты, которые есть у всех представителей сущности (например, id, common_attribute1, common_attribute2, common_attribute3), а вторая таблица содержит уникальные атрибуты для каждого представителя, а также foreign key, связывающий их с общими атрибутами.
Такая структура таблиц позволяет эффективно хранить информацию об общих и уникальных атрибутах сущности, обеспечивая удобный доступ к данным и их связывание.