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.
