Разработка схемы "Заказ", в которой есть разные таблицы определения продукта

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

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

Продукт X может иметь идентифицирующие характеристики, такие как (гипотетически)

  • 'Цвет',
  • "Материал"
  • "Среднее время до отказа"

но продукт Y может иметь характеристики

  • "Толщина",
  • 'Диаметр'
  • "Источник питания"

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

Вещи, которые я пробовал в прошлом

  • Создайте родительскую таблицу с именем "Product" со столбцами, общими для Product X и Product Y, затем используйте "Product" в качестве ссылки для таблицы Order Line и создайте отношение FK с "Product" в качестве первичной стороны между таблицами для Product X и Продукт Y. Это, в основном, помещает таблицу "Продукт" как родительский элемент как для Order Line, так и для всех разнородных таблиц продуктов (например, Продукты X и Y). Он отлично работает при вводе заказа, но вызывает проблемы с отчетностью или редактированием заказа, поскольку запись "Продукт" должна отслеживать, какой это тип продукта, чтобы определить, как присоединить "Продукт" к более подробному дочернему элементу, продукту Х или продукту. Y. Преимущества: ключевые отношения сохраняются. Недостатки: отчетность, редактирование на уровне строки заказа / продукта.
  • Создайте столбцы "Тип продукта" и "Ключ продукта" на уровне строки заказа, а затем используйте логику или представления CASE для определения настроенного продукта, к которому относится строка. Это похоже на пункт (1) без общей таблицы "Продукт". Я считаю это более "быстрым и грязным" решением, поскольку оно полностью устраняет внешние ключи между строками заказа и определениями их продуктов. Преимущества: быстрое решение. Недостатки: то же, что в пункте (1), плюс потерянный RI.
  • Гомогенизируйте определения продукта, создав общую таблицу заголовков и используя пары ключ / значение для настраиваемых атрибутов (OrderLine [n] <- [1] Product [1] <- [n] ProductAttribute). Преимущества: ключевые отношения сохраняются; нет двусмысленности относительно определения продукта. Недостатки: создание отчетов (например, получение списка продуктов с их атрибутами), типизация данных значений атрибутов, производительность (выбор атрибутов продукта, вставка или обновление атрибутов продукта и т. Д.)

Если бы кто-то попробовал другую стратегию с большим успехом, я бы хотел услышать об этом.

Спасибо.

Источник

5 ответов

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

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

Третье решение, которое вы описываете, включающее сохранение имени атрибута в виде строки, представляет собой проект под названием "Entity-Attribute-Value", и вы можете сказать, что это болезненное и дорогое решение. Нет способа обеспечить целостность данных, нет способа сделать один атрибут NOT NULL, нет способа убедиться, что данный продукт имеет определенный набор атрибутов. Нет способа ограничить один атрибут для таблицы поиска. Многие типы агрегатных запросов становятся невозможными в SQL, поэтому для написания отчетов приходится писать много кода приложения. Используйте дизайн EAV только в том случае, если вам необходимо, например, если у вас есть неограниченное количество типов продуктов, список атрибутов может быть разным в каждой строке, и ваша схема должна часто содержать новые типы продуктов без изменения кода или схемы.

Другое решение - "Наследование за одним столом". При этом используется чрезвычайно широкая таблица со столбцом для каждого атрибута каждого продукта. Оставьте NULL в столбцах, которые не имеют отношения к продукту в данной строке. Это фактически означает, что вы не можете объявить атрибут как NOT NULL (если он не входит в группу, общую для всех продуктов). Кроме того, большинство продуктов РСУБД имеют ограничение на количество столбцов в одной таблице или общую ширину в байтах строки. Таким образом, вы ограничены в количестве типов продуктов, которые вы можете представить таким образом.

Существуют гибридные решения, например, обычные атрибуты можно хранить в столбцах, но атрибуты для конкретного продукта в таблице Entity-Attribute-Value. Или вы можете хранить специфичные для продукта атрибуты другим структурированным способом, таким как XML или YAML, в столбце BLOB таблицы Products. Но эти гибридные решения страдают, потому что теперь некоторые атрибуты должны выбираться по-другому

Окончательное решение для подобных ситуаций - использование семантической модели данных с использованием RDF вместо реляционной базы данных. Это имеет некоторые общие характеристики с EAV, но гораздо более амбициозно. Все метаданные хранятся так же, как и данные, поэтому каждый объект самоописывается, и вы можете запрашивать список атрибутов для данного продукта так же, как вы запрашиваете данные. Существуют специальные продукты, такие как Jena или Sesame, реализующие эту модель данных и специальный язык запросов, отличный от SQL.

Источник

Это может начать вас. Это потребует некоторой доработки

Table Product ( id PK, name, price, units_per_package)
Table Product_Attribs (id FK ref Product, AttribName, AttribValue)

Что позволит вам прикрепить список атрибутов к продуктам. - Это по сути ваш вариант 3

Если вы знаете максимальное количество атрибутов, вы можете пойти

Table Product (id PK, name, price, units_per_package, attrName_1, attrValue_1 ...)

Что, конечно, приведет к ненормализации базы данных, но упростит запросы.

Я предпочитаю первый вариант, потому что

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

Там нет волшебной пули, которую вы пропустили.

У вас есть то, что иногда называют "непересекающимися подклассами". Есть суперкласс (Product) с двумя подклассами (ProductX) и (ProductY). Это проблема, которая - для реляционных баз данных - действительно сложная. [Другая сложная проблема - Билль о материалах. Другая сложная проблема - это графы узлов и дуг.]

Вы действительно хотите полиморфизм, где OrderLine связан с подклассом Product, но не знает (или не заботится), какой конкретный подкласс.

У вас не так много вариантов для моделирования. Вы в значительной степени определили плохие черты каждого из них. Это почти вся вселенная выбора.

  1. Подтолкнуть все до суперкласса. Это единый табличный подход, в котором у вас есть Product с дискриминатором (type="X" и type="Y") и миллионом столбцов. Столбцы Product - это объединение столбцов в ProductX и ProductY. Повсюду будут пустые значения из-за неиспользуемых столбцов.

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

  3. Присоедините экземпляр суперкласса к экземпляру подкласса. В этом случае таблица Product является пересечением столбцов ProductX и ProductY. Каждый продукт имеет ссылку на ключ в ProductX или ProductY.

На самом деле нет смелого нового направления. В мировоззрении реляционных баз данных это - выбор.

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

Преимущество заключается в том, что вы можете описать свою обработку кратко и правильно. Как объекты, с отношениями подкласса.

Недостатком является то, что ваш SQL превращается в упрощенные массовые выборки, обновления и вставки.

Это становится преимуществом, когда SQL изолируется на уровне ORM и управляется как тривиальная деталь реализации. Java-программисты используют iBatis (или Hibernate, TopLink или Cocoon), Python-программисты используют SQLAlchemy или SQLObject. ORM выполняет выборку и сохранение базы данных; Ваше приложение напрямую манипулирует заказами, линиями и продуктами.

Источник

Меняется ли когда-либо ваша линейка продуктов?
Если это произойдет, то создание таблицы для каждого продукта будет стоить вам очень дорого, и идея пары ключ / значение вам пригодится. Это направление, в котором я, естественно, нарисован.

Я хотел бы создать таблицы, как это:

Attribute(attribute_id, description, is_listed)    
-- contains values like "colour", "width", "power source", etc. 
-- "is_listed" tells us if we can get a list of valid values: 

AttributeValue(attribute_id, value)
-- lists of valid values for different attributes.  

Product (product_id, description)

ProductAttribute (product_id, attribute_id)  
-- tells us which attributes apply to which products

Order (order_id, etc)

OrderLine (order_id, order_line_id, product_id)

OrderLineProductAttributeValue (order_line_id, attribute_id, value)
-- tells us things like: order line 999 has "colour" of "blue"

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

Мы делаем подобные вещи с несколькими типами сущностей.

Источник

Крис и AJ: Спасибо за ваши ответы. Линейка продуктов может измениться, но я бы не назвал ее "изменчивой".

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

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

Еще раз спасибо за ваши ответы!

Источник
Другие вопросы по тегам