Jak zaprojektować schemat bazy danych obsługujący tagowanie kategoriami?

Jest to kolejna odmiana projektuEncja-atrybut-wartość .

Bardziej rozpoznawalna tabela EAV wygląda następująco:

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

Niektórzy zmuszają attr_name do odwoływania się do tabeli z predefiniowanymi nazwami atrybutów, aby ograniczyć chaos.

To, co zrobiłeś, to po prostu rozłożenie tabeli EAV na trzy tabele, ale bez poprawy kolejności metadanych:

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"
);

Jeśli zamierzasz używać projektu EAV, potrzebujesz tylko vehicleTags i categories stoły.

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

Ale pamiętaj, że mieszasz dane z metadanymi. Tracisz możliwość zastosowania pewnych ograniczeń do modelu danych.

• Jak sprawić, by jedna z kategorii była obowiązkowa (konwencjonalna kolumna używa ograniczenia NOT NULL)?
• Jak można używać typów danych SQL do walidacji niektórych wartości znaczników? Nie możesz, ponieważ używasz długiego ciągu dla każdej wartości znacznika. Czy ten ciąg jest wystarczająco długi dla każdego znacznika, którego potrzebujesz w przyszłość? Nie wiadomo.
• jak można ograniczyć niektóre znaczniki do zestawu dozwolonych wartości(konwencjonalna tabela używa klucza obcego do tabeli wyszukiwania)? To jest twój przykład "softtop" vs. "soft top". Ale nie można utworzyć ograniczenia w kolumnie tag, ponieważ ograniczenie to ma zastosowanie do wszystkich innych wartości znaczników dla innych kategorii. Skutecznie ograniczyłbyś rozmiar silnika i kolor lakieru do "miękkiego topu".

Bazy danych SQL nie działają dobrze z tym modelem. Jest to niezwykle trudne, aby uzyskać prawo, a zapytanie staje się bardzo złożone. Jeśli nadal używasz SQL, lepiej będzie modelować tabele konwencjonalnie, z jedną kolumną na atrybut. Jeśli musisz mieć "podtypy", zdefiniuj podrzędną tabelę dla podtypu (dziedziczenie klas), lub użyj dziedziczenie pojedynczych tabel. Jeśli masz nieograniczoną zmienność atrybutów dla jednostki, użyj Serialized LOB.

Inny technologia, która jest zaprojektowana dla tego rodzaju płynnych, nie relacyjnych modeli danych, to semantyczna baza danych, przechowująca dane w RDF i zapytana o SPARQL. Jednym wolnym rozwiązaniem jest Sezam .