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

Это еще один вариант дизайна 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 для проверки некоторых значений ваших тегов? Вы не можете, потому что вы используете длинную строку для каждого значения тега. Достаточно ли этой строки для каждого тега, который вам понадобится в будущем? Вы не можете сказать.
• Как вы можете ограничить некоторые из ваших тегов набором разрешенных значений (обычная таблица использует внешний ключ для таблицы поиска)? Это ваш пример «мягкого верха» и «мягкого верха». Но вы не можете наложить ограничение на столбец tag, потому что это ограничение будет применяться ко всем другим значениям тегов для других категорий. Вы бы фактически ограничили размер двигателя и цвет краски до «мягкого верха».

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

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

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

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

Например, №1 поле тега может содержать такое значение, как:

производство_Mercedes, модель_SLK32 AMG, кабриолет, жесткая крыша

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

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

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

vehicle
  vid
  vin
  make
  model

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

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).

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

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

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