przy wielu projektach pojawia się potrzeba przechowywania zmiennej listy “cech" jakichś obiektów.

weźmy na przykład sklep internetowy: mamy jakieś tam kategorie (struktura drzewiasta, moja ulubiona 🙂 ), w nich produkty. każdy produkt ma pewne cechy stałe – cena, tytuł, opis. natomiast produkty w określonych kategoriach mają swoje własne cechy dodatkowe.

np. dla samochodów możemy chcieć przechowywać:

• ilość drzwi
• pojemność silnika
• typ paliwa
• rodzaj skrzyni biegów

z drugiej strony, ogłoszenia w kategorii komputery będą miały pola takie jak:

• ilość pamięci
• wielkość dysku
• typ procesora

najprostszym rozwiązaniem jest trzymanie produktów w każdej kategorii w oddzielnej tabelce – gdzie każda z tych tabelek ma różną strukturę (inna lista pól).

jest to rozwiązanie nieakceptowalne – osobiście uważam, że jakiekolwiek rozwiązanie zakładające modyfikacje struktury bazy danych w trakcie normalnego użytkowania jest błędne.

inną metodą jest zrobienie sobie tabelki typu:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha1     TEXT,
cecha2     TEXT,
cecha3     TEXT,
cecha4     TEXT,
...
);

i pilnowanie, że dla danego produktu kolumna cecha1 oznacza pojemność silnika, a dla innego jest to ilość pamięci.

takie rozwiązanie ma swoje zalety – najważniejszą jest to, że aby wyciągnąć informacje o wszystkich polach dla danego produktu wystarczy pobrać jeden rekord z bazy.

ale dopóki nie obsługujesz miliona page-views dziennie w swoim sklepie – ten problem jest mało istotny 🙂

zdecydowanie najskuteczniejszą metodą jest tabelka:

 CREATE TABLE zmienne_cechy (
id         SERIAL PRIMARY KEY,
produkt_id INT  NOT NULL REFERENCES produkty(id),
cecha      TEXT,
wartosc    TEXT
);

taka tabelka w jednej prostej strukturze pozwala na zapisanie wszystkich mozliwych cech i łatwe wyszukiwanie. no właśnie. czy na pewno łatwe?

tabelka pokazana taka jak tu – jest może i fajna, ale brakuje jej jeszcze jednej rzeczy:

create unique index ui_zmienne_cechy_pic on zmienne_cechy (produkt_id, cecha);

jeśli nie czytacie sql'i ze 100% zrozumieniem, to powyższe powoduje, że dany produkt może mieć tylko jedną wartość danej cechy. może mieć dowolnie wiele cech, ale żadna z cech nie może mieć wielu wartości.

zazwyczaj takie ograniczenie w niczym nie przeszkadza. zdarzają się czasem (ale bardzo rzadko) sytuacje, że istnieje potrzeba wielu wartości jednej cechy – sugeruję by wtedy nie kasować tego indeksu/klucza unikalnego tylko po prostu użyć ciut innych cech.

czemu?

otóż taka tabelka z pokazanym kluczem unikalnym pozwala nam w trywialny sposób zrobienie tego co bez klucza jest dużo trudniejsze (zasobochłonne): znalezienia produktów w/g kilku cech jednocześnie.

załóżmy, że chcemy znaleźć samochody o pojemności silnika 2000 z automatyczną skrzynią biegów.

bez klucza unikalnego jesteśmy skazani na coś takiego:

 SELECT zc1.produkt_id
FROM zmienne_cechy zc1 JOIN zmienne_cechy zc2 ON zc1.produkt_id = zc2.produkt_id
WHERE zc1.cecha='pojemnosc silnika' AND zc1.wartosc = '2000' AND zc2.cecha = 'skrzynia biegow' AND zc2.wartosc = 'automat';

nie jest to oczywiście takie złe. ale przy dużej ilości produktów stanie się problematyczne. nie mówiąc o tym jak będziemy chcieli sprawdzić produkty w/g np. 5 cech na raz. 5 joinów? jeśli w bazie jest np. 1000 produktów i każdy ma średnio 10 cech, to łączymy 5 razy ze sobą tabelę o 50000 rekordów. i szukamy na nich wszystkich. mało przyjemne.

dodanie wspomnianego wyżej klucza unikalnego pozwala na użycie w naszym select'cie rzadko używanej (i słabo znanej) klauzuli HAVING:

 SELECT produkt_id
FROM zmienne_cechy
WHERE
(cecha='pojemnosc silnika' AND wartosc='2000')
OR
(cecha='skrzynia biegow' AND wartosc = 'automat')
GROUP BY produkt_id
HAVING COUNT(*) = 2;

powstałe zapytanie ma kilka zalet:

• jest trywialnie rozbudowywalne o kolejne cechy – bez konieczności dodatkowych joinów = wystarczy dodać dodatkowe warunki i podbić wartość w klauzuli having
• jeśli używamy postgresql'a 8.1 i mamy dodatkowo indeks dwupoowy na (cecha, wartosc), to postgresql uzyje bardzo szybkich bitmap-or'ów

moje testy wykazały, że na postgresie 8.1 obie metody dają bardzo podobne wyniki (chodzi o czas zapytania) jeśli szukamy dwóch cech, natomiast już od 3 przewaga rozwiązania z having jest olbrzymia.