Как создать схему базы данных для поддержки тегов с помощью категорий?

Ответ 1

Это еще одна вариация дизайна Entity-Attribute-Value.

Более узнаваемая таблица EAV выглядит следующим образом:

CREATE TABLE vehicleEAV (
  vid        INTEGER,
  attr_name  VARCHAR(20),
  attr_value VARCHAR(100),
  PRIMARY KEY (vid, attr_name),
  FOREIGN KEY (vid) REFERENCES vehicles (vid)
);

Некоторые люди вынуждают attr_name ссылаться на таблицу поиска предопределенных имен атрибутов, чтобы ограничить хаос.

Вы сделали просто распространение таблицы EAV по трем таблицам, но без улучшения порядка ваших метаданных:

CREATE TABLE vehicleTag (
  vid         INTEGER,
  cid         INTEGER,
  tid         INTEGER,
  PRIMARY KEY (vid, cid),
  FOREIGN KEY (vid) REFERENCES vehicles(vid),
  FOREIGN KEY (cid) REFERENCES categories(cid),
  FOREIGN KEY (tid) REFERENCES tags(tid)
);

CREATE TABLE categories (
  cid        INTEGER PRIMARY KEY,
  category   VARCHAR(20) -- "attr_name"
);

CREATE TABLE tags (
  tid        INTEGER PRIMARY KEY,
  tag        VARCHAR(100) -- "attr_value"
);

Если вы собираетесь использовать дизайн EAV, вам понадобятся таблицы vehicleTags и categories.

CREATE TABLE vehicleTag (
  vid         INTEGER,
  cid         INTEGER,     -- reference to "attr_name" lookup table
  tag         VARCHAR(100, -- "attr_value"
  PRIMARY KEY (vid, cid),
  FOREIGN KEY (vid) REFERENCES vehicles(vid),
  FOREIGN KEY (cid) REFERENCES categories(cid)
);

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

• Как вы можете сделать одну из категорий обязательной (обычный столбец использует ограничение NOT NULL)?
• Как вы можете использовать типы данных SQL для проверки некоторых значений ваших тегов? Вы не можете, потому что вы используете длинную строку для каждого значения тега. Является ли эта строка достаточно длинной для каждого тега, который вам понадобится в будущем? Вы не можете сказать.
• Как вы можете ограничить некоторые из ваших тегов набором допустимых значений (обычная таблица использует внешний ключ для таблицы поиска)? Это ваш пример "softtop" и "soft top". Но вы не можете установить ограничение на столбец tag, потому что это ограничение применимо ко всем другим значениям тегов для других категорий. Вы эффективно ограничиваете размер двигателя и цвет краски "мягким верхом".

Базы данных SQL не работают с этой моделью. Чрезвычайно сложно получить право, и его запрос становится очень сложным. Если вы продолжаете использовать SQL, вам лучше будет моделировать таблицы традиционно, с одним столбцом на атрибут. Если вам нужно иметь "подтипы", тогда определите подчиненную таблицу для каждого подтипа ( "Наследование классов" ), либо используйте Наследование отдельных таблиц. Если у вас есть неограниченное изменение атрибутов для объекта, используйте Сериализованное LOB.

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

Ответ 2

Мне нужно было решить эту точную проблему (такую ​​же общую область и все - автозапчасти). Я обнаружил, что лучшим решением проблемы было использование Lucene/Xapian/Ferret/Sphinx или любого индексатора, который вы предпочитаете. Гораздо лучше производительность, чем может предложить SQL.

Ответ 3

Я думаю, ваше решение состоит в том, чтобы просто добавить колонку производителя в таблицу ваших автомобилей. Это признак того, что вы знаете, что все транспортные средства будут иметь (например, автомобили самопроизвольно не появляются сами по себе), и, сделав столбец на вашем автомобильном столе, вы решаете проблему наличия одного и только одного производителя для каждого автомобиля. Этот подход применим к любым атрибутам, которые, как вы знаете, будут использоваться всеми транспортными средствами. Затем вы можете реализовать систему тегов для других атрибутов, которые не являются универсальными.

Таким образом, взяв из вашего примера, стол автомобиля будет выглядеть примерно так:

vehicle
  vid
  vin
  make
  model

Ответ 4

Одним из способов было бы немного переосмыслить вашу схему, нормализуя ключи тегов от значений:

vehicles
  int vid
  string vin

tags
  int tid
  int cid
  string key

categories
  int cid
  string category

vehicleTags
  int vid
  int tid
  string value

Теперь вам нужно только уникальное ограничение на vehicleTags(vid, tid).

В качестве альтернативы существуют способы создания ограничений, отличных от простых внешних ключей: в зависимости от вашей базы данных вы можете написать настраиваемое ограничение или триггер insert/update для обеспечения уникальности тегов транспортных средств?

Ответ 5

Мне нужно было решить эту точную проблему (такую ​​же общую область и все - автозапчасти). Я обнаружил, что лучшим решением проблемы было использование Lucene/Xapian/Ferret/Sphinx или любого индексатора, который вы предпочитаете. Гораздо лучше производительность, чем может предложить SQL.

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