Projektowanie baz danych do tagowania

Oto dobry artykuł o oznaczaniu schematów baz danych:

Http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/

Wraz z testami wydajności:

Http://howto.philippkeller.com/2005/06/19/Tagsystems-performance-tests/

Zauważ, że wnioski są bardzo specyficzne dla MySQL, który (przynajmniej w 2005 roku w czasie, gdy został napisany) miał bardzo słabą charakterystykę indeksowania pełnotekstowego.

Nie widzę problemu z prostym rozwiązaniem: Table for items, table for tags, crosstable for "tagging"

Wskaźniki w tabeli krzyżowej powinny wystarczyć do optymalizacji. Wybór odpowiednich elementów to

SELECT * FROM items WHERE id IN  
    (SELECT DISTINCT item_id FROM item_tag WHERE  
    tag_id = tag1 OR tag_id = tag2 OR ...)  

I oznaczanie będzie

SELECT * FROM items WHERE  
    EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag1)  
    AND EXISTS (SELECT 1 FROM item_tag WHERE id = item_id AND tag_id = tag2)  
    AND ...

Co jest prawdą, nie jest tak skuteczne dla dużej liczby porównujących tagów. Jeśli chcesz zachować liczbę znaczników w pamięci, możesz wykonać zapytanie, aby rozpocząć od znaczników, które nie są często, więc sekwencja będzie oceniana szybciej. W zależności od oczekiwanej liczby tagów do dopasowania i oczekiwanej długości dopasowania dowolnego z nich, może to być OK rozwiązanie, jeśli chcesz dopasować tagi 20 i oczekiwać, że jakiś losowy element będzie pasował do 15 z nich, to nadal będzie to ciężkie w bazie danych.

Chciałem tylko podkreślić, że artykuł, do którego nawiązuje @Jeff Atwood ( http://howto.philippkeller.com/2005/04/24/Tags-Database-schemas/) jest bardzo dokładny (omawia zalety 3 różnych podejść schematu) i ma dobre rozwiązanie dla zapytań AND, które zwykle będą działać lepiej niż to, co zostało wymienione tutaj do tej pory (tzn. nie używa skorelowanych zapytań podrzędnych dla każdego terminu). Również wiele dobrych rzeczy w komentarzach.

Ps-podejście, które każdy jest mowa tutaj jest określana jako rozwiązanie "Toxi" w artykule.

Możesz poeksperymentować z nie-ściśle-bazodanowym rozwiązaniem, takim jak implementacja Java Content repozytorium (np. Apache Jackrabbit) i użyć wyszukiwarki zbudowanej na tym, jak Apache Lucene .

To rozwiązanie z odpowiednimi mechanizmami buforowania prawdopodobnie przyniosłoby lepszą wydajność niż rozwiązanie domowe.

Nie wydaje mi się jednak, aby w małej lub średniej wielkości aplikacji wymagało to bardziej wyrafinowanego implementacja niż znormalizowana baza danych wspomniana we wcześniejszych postach.

EDIT: z Twoim wyjaśnieniem bardziej przekonujące wydaje się użycie rozwiązania podobnego do JCR z wyszukiwarką. To znacznie uprościłoby Twoje programy na dłuższą metę.

Najprostszą metodą jest utworzenie tabeli tagów.
Target_Type -- w przypadku tagowania wielu tabel
Target -- Klucz do zapisu oznaczany
Tag -- tekst znacznika

Odpytywanie danych byłoby czymś w stylu:

Select distinct target from tags   
where tag in ([your list of tags to search for here])  
and target_type = [the table you're searching]

UPDATE
W oparciu o twoje wymagania i warunki, powyższe zapytanie zmieniłoby się w coś takiego

select target
from (
  select target, count(*) cnt 
  from tags   
  where tag in ([your list of tags to search for here])
    and target_type = [the table you're searching]
)
where cnt = [number of tags being searched]

Popieram sugestię @Zizzencs, że możesz chcieć czegoś, co nie jest całkowicie (R)DB-centric

Jakoś wierzę, że używanie zwykłych pól nvarchar do przechowywania tagów z odpowiednim buforowaniem / indeksowaniem może przynieść szybsze wyniki. Ale to tylko ja.

Zaimplementowałem systemy tagowania za pomocą 3 tabel do reprezentowania relacji wiele do wielu (Znaczniki pozycji ItemTags), ale przypuszczam, że będziesz miał do czynienia ze znacznikami w wielu miejscach, mogę ci powiedzieć, że z 3 tabelami konieczność jednoczesnego manipulowania/zadawania pytań przez cały czas na pewno sprawi, że Twój kod będzie bardziej złożony.

Warto rozważyć, czy dodana złożoność jest tego warta.

Nie będziesz w stanie uniknąć łączenia i nadal być nieco znormalizowany.

Moim podejściem jest posiadanie tabeli tagów.

 TagId (PK)| TagName (Indexed)

Następnie masz kolumnę TagXREFID w tabeli elementów.

Ta kolumna TagXREFID to FK do 3 tabeli, nazwę ją TagXREF:

 TagXrefID | ItemID | TagId

Więc, aby uzyskać wszystkie tagi dla elementu byłoby coś w stylu:

SELECT Tags.TagId,Tags.TagName 
     FROM Tags,TagXref 
     WHERE TagXref.TagId = Tags.TagId 
         AND TagXref.ItemID = @ItemID

I aby uzyskać wszystkie elementy dla tagu, użyłbym czegoś takiego:

SELECT * FROM Items, TagXref
     WHERE TagXref.TagId IN 
          ( SELECT Tags.TagId FROM Tags
                WHERE Tags.TagName = @TagName; )
     AND Items.ItemId = TagXref.ItemId;

To i kilka tagów razem, można by zmodyfikować powyższe stwierdzenie nieco dodać i Tagi.TagName = @ TagName1 i Tagi.TagName = @TagName2 itd...i dynamicznie budować zapytanie.

To, co lubię robić, to mieć wiele tabel, które reprezentują surowe dane, więc w tym przypadku masz

Items (ID pk, Name, <properties>)
Tags (ID pk, Name)
TagItems (TagID fk, ItemID fk)

To działa szybko dla czasu zapisu i utrzymuje wszystko znormalizowane, ale możesz również zauważyć, że dla każdego znacznika, będziesz musiał połączyć tabele dwa razy dla każdego kolejnego znacznika, który chcesz i, więc ma powolny odczyt.

Rozwiązaniem poprawiającym odczyt jest utworzenie tabeli buforowania na komendzie poprzez ustawienie procedury składowanej, która zasadniczo tworzy nową tabelę, która reprezentuje dane w spłaszczonym formacie...

CachedTagItems(ID, Name, <properties>, tag1, tag2, ... tagN)

Następnie możesz rozważyć, jak często tabela Tagowanych elementów musi być aktualizowana, jeśli znajduje się na każdej wstawce, a następnie wywołać procedurę składowaną w zdarzeniu Wstaw kursor. Jeśli jest to zadanie godzinowe, skonfiguruj zadanie godzinowe, aby je uruchomić.

Teraz, Aby uzyskać naprawdę sprytne w odzyskiwaniu danych, będziesz chciał utworzyć procedurę składowaną, aby uzyskać dane z tagów. Zamiast korzystać z zagnieżdżonych zapytań w instrukcji massive case, chcesz przekazać pojedynczą parametr zawierający listę znaczników, które chcesz wybrać z bazy danych i zwracający zestaw rekordów pozycji. Najlepiej byłoby w formacie binarnym, używając operatorów bitowych.

W formacie binarnym jest to łatwe do wyjaśnienia. Załóżmy, że są cztery znaczniki przypisane do elementu, w binarnym możemy reprezentować, że

0000

Jeśli wszystkie cztery znaczniki są przypisane do obiektu, obiekt będzie wyglądał tak...

1111

1100

To tylko przypadek znajdowanie wartości binarnych z 1S i zerami w kolumnie, którą chcesz. Korzystając z operatorów bitowych SQL Server, możesz sprawdzić, czy w pierwszej z kolumn znajduje się 1 za pomocą bardzo prostych zapytań.

Sprawdź ten link, aby dowiedzieć się więcej.

Parafrazując to, co powiedzieli inni: sztuczka nie znajduje się w schemacie , jest w zapytaniu .

Naiwny schemat encji / etykiet/znaczników jest właściwym rozwiązaniem. Ale jak już widziałeś, nie jest od razu jasne, jak wykonać zapytanie i za pomocą wielu tagów.

Najlepszy sposób optymalizacji tego zapytania będzie zależny od platformy, więc polecam ponowne tagowanie pytania z RDBS i zmiana tytułu na coś w rodzaju " optymalny sposób wykonywania i zapytań na baza tagów"

Mam kilka sugestii dotyczących MS SQL, ale powstrzymam się w przypadku, gdy nie jest to platforma, z której korzystasz.

Wariacją powyższej odpowiedzi jest pobranie identyfikatorów tagów, posortowanie ich, połączenie jako oddzielony łańcuch ^ i skrócenie ich. Następnie po prostu powiązaj hash z elementem. Każda kombinacja tagów tworzy nowy klucz. Aby wykonać wyszukiwanie i wyszukiwanie, po prostu utwórz hash z podanymi identyfikatorami tagów i wyszukaj. Zmiana znaczników na elemencie spowoduje odtworzenie hasha. Elementy z tym samym zestawem znaczników mają ten sam klucz skrótu.

Jeśli masz typ tablicy, możesz wstępnie zagregować potrzebne dane. Zobacz tę odpowiedź w osobnym wątku:

Jaka jest użyteczność typu array?