Проблема с схемой базы данных

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

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

Одна таблица продуктов с идентификатором, именем и описанием.

Одна таблица свойств с идентификатором, Product_ID, Property и Value.

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

Ответы

Ответ 1

Это действительно движение к Шестой Нормальной Форме, просто люди, подобные вам, у которых нет академического или эмпирического фона, не знают (a) имя для него и (b) правила и предостережения. Такие люди реализовали то, что обычно известно как Entity-Attribute-Value или EAV. Если все сделано правильно, это нормально, и есть много тысяч медицинских систем, где есть диагностические данные и информация о дозировке в таких таблицах. Если это не так, то это один завтрак для собак, который можно использовать и поддерживать.

  • Сначала убедитесь, что у вас есть Product в true и full 5NF.

  • Всегда используйте полную декларативную ссылочную целостность; CHECK и RULES.

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

  • Кроме того, любые ассоциативные таблицы (где имеется множественная ссылка на другую таблицу [например, поставщик]) должны быть отдельными.

    • Я предоставляю модель данных, которая обсуждает полный контроль; он включает простой каталог, который может использоваться для проверки, а также для навигации. Вам нужно добавить все CHECK Constraint и RULE, чтобы гарантировать, что данные и ссылочная целостность не будут потеряны. Это означает, например:
      • для столбца CPUSpeed, который хранится в ProductDecimal, CHECK, что он находится в правильном диапазоне значений
      • для каждой таблицы Product CHECK, что DataType верен для комбинации ProductType-ColumnNo
    • Эта структура лучше, чем большинство EAV, и не совсем полный 6NF.
      ,

  • Сохраните все обязательные столбцы в Product; используйте таблицы sub-Product только для дополнительных столбцов.

  • Для каждой такой таблицы (например, Product) вам необходимо создать представление (пунктирная линия), которое построит строки 5NF из таблиц EAV/6NF. У вас может быть несколько просмотров: Product_CPU, Product_Disk.

  • Не обновлять через представление. Храните все ваши обновления в транзакции в хранимой процедуре и вставляйте или обновляйте каждый из столбцов (т.е. Таблицы Product и sub-Product, которые применимы для каждого конкретного ProductType) вместе.

  • Гигантский? Коммерческие базы данных (а не бесплатное ПО) не имеют проблем с большими таблицами или объединениями. Это фактически очень эффективная структура и позволяет очень быстрый поиск, потому что таблицы фактически ориентированы на столбцы (не ориентированы на строки). Если население гигантское, то оно гигантское, сделайте свою собственную арифметику.

  • Вам нужна еще одна таблица, таблица поиска для Property (или атрибута). Это часть каталога и основана на ProductType

Лучшее решение - пойти на полную, формальную Шестой Нормальную форму. Не обязательно, если у вас есть только одна или несколько таблиц, для которых требуются дополнительные столбцы.

Чтобы быть ясным:

  • Шестая нормальная форма Строка состоит из Первичного ключа и, самое большее, одного Атрибута.

  • Это 6NF (по крайней мере для кластера таблиц Product), а затем Normalized снова (не в смысле нормальной формы) с помощью DataType, чтобы уменьшить количество таблиц (в противном случае у вас будет одна таблица для каждого атрибута).

  • Это сохраняет полное управление Rdb (FKs, ограничения и т.д.); тогда как общие типы EAV не беспокоят DRI и управление.

  • У этого также есть зачатки каталога.

Ссылка на модель данных кластера продуктов

Ссылка на IDEF1X Notation для тех, кто не знаком с стандартом реляционного моделирования.

Update

Вам может быть интересно это ▶ Обсуждение 5NF 6NF ◀. Я напишу это в какой-то момент.

Ответ 2

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

Однако я не заинтересован в том, чтобы иметь значение, хранящееся рядом с каждым свойством, как 1:1. Лучше всего, если у вас есть таблица propertyvalue, которая связывает свойство со значением. Затем вы выровняете таблицу productproperty в пользу наличия более богатой таблицы productpropertyvalue, которая могла бы полностью описать взаимосвязь между продуктом, его свойствами и их значениями.

Возможно, тогда у вас может быть следующее:

product => (ID (unique key), Name, Description)
property => (ID (unique key), Description)
propertyvalue => (ID (unique key), propertyID (foreign key), value)
productpropertyvalue => (ID (unique key), productID (foreign key), propertyValueID (foreign key))

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