Dynamiczne atrybuty rekordów w SQL Server

Wszystko ma być dynamiczne

Dużo jest różnych marketingowych haseł w różnych branżach. Gdyby tak zestawić hasła reklamowe w branży IT i określić pierwszą dziesiątkę najczęściej pojawiających się haseł, znalazłoby się tam z pewnością słowo dynamiczne. Dynamiczne rekordy, dynamiczne formularze, dynamicznie doczytujące się dane, dynamicznie generujące się raporty, dynamicznie dodawane i pobierane atrybuty. W językach obiektowych istnieje dużo różnych mechanizmów pozwalających na realizację dynamiczności. Pojawiająca się potrzeba prowadzi do powstawania różnych rozwiązań, z czego jedna są lepsze, inne gorsze. Jedną z tych metod, dość często wykorzystywanych, nazywa się modelem EAV (ang. Entity Attribute Value, Encja Atrybut Wartość). W podstawowej postaci składa się on z trzech tabel reprezentujących kolejno: encje (tabele), atrybuty i wartości konkretnych atrybutów wybranych encji. Brzmi to może trochę enigmatycznie, ale zaraz się wyjaśni.

Tabela musi mieć zdefiniowane kolumny

Postawmy sobie do rozwiązania pewien problem. Załóżmy, że mamy sklep wielobranżowy, z różnymi towarami. Mamy narty, które powinny mieć zdefiniowaną długość, cenę, mamy samochód, który powinien mieć popdaną pojemność silnika i rok produkcji, mamy jabłka, które powinny mieć podany gatunek i kraj pochodzenia. Jak te wszystkie obiekty przechowywać w bazie? Czy stworzyć dla nich oddzielne tabele? Nie jest to chyba wykonalne, bo nie wiemy ile mamy produktów. Poza tym dodanie produktu będzie w takim przypadku wymagało dodania nowej tabeli w bazie danych i zestawienia relacji. Nie jest to chyba dobre rozwiązanie. A może by tak te produkty umieścić w jednej tabeli i dodać kolumnę dla każdego atrybutu? Te, które nie będą używane będą miały wartość NULL. O ile zestaw atrybutów jest z góry określony i nie jest ich dużo, można takie rozwiązanie przyjąć. Pomóc tutaj może mechanizm kolumn rozrzedzonych (SPARSE), ale dalej nie jest to rozwiązanie w pełni dynamiczne. Bo gdyby w przyszłości jakiś kreatywny kierownik uznał, że potrzebujemy jeszcze jednego atrybutu - co począć? Trzeba dodać jeszcze jedną kolumnę i obsłużyć ją w zapytaniach. Wymaga to ingerencji w kod aplikacji. Naturalne jest, że rozwiązanie pewnych problemów wymaga poświęceń. Dynamiczne rozwiązania powstają zwykle kosztem pracy zespołu i wydajności aplikacji. Problem dynamicznych kolumn w bazie wymaga oprócz tego rozluźnienia więzów integralności i kontroli typów. Są wprawdzie wyzwalacze, które mogą tego wszystkiego pilnować, ale jest to temat na dłuższą pogawędkę.

Model Entity Attribute Value

Żeby nie przedłużać i tak już długiego wstępu przejdę do konkretnego przykładu. Przypuśćmy, że mamy zdefiniowany jakieś elementy, powiedzmy towary:

CREATE TABLE SomeItem
(
ID int IDENTITY PRIMARY KEY,
Name nvarchar(10) NOT NULL
)

INSERT INTO SomeItem VALUES ('Rower')
INSERT INTO SomeItem VALUES ('Komputer')

Towary te mogą mieć atrybuty. Wspomniałem wcześniej, że tych atrybutów może być dużo i nie wiadomo jakiego one będą typu. A gdyby tak każdy atrybut powiązać z rekordem relacją typu jeden do wielu? To zbliża nas do modelu Entity Attribute Value. Popatrzmy na przykładową relację:

CREATE TABLE EntityAttributeValue
(
  ParentID int NOT NULL,
  Property nvarchar(20),
  Value sql_variant,
  CONSTRAINT FK_EAV_SomeItem
  FOREIGN KEY (ParentID)
  REFERENCES SomeItem(ID)
)

INSERT INTO EntityAttributeValue VALUES (1, 'Kolor', '#FF0000');
INSERT INTO EntityAttributeValue VALUES (1, 'Data produkcji', '01-01-2010');
INSERT INTO EntityAttributeValue VALUES (1, 'Cena', 989.99);
INSERT INTO EntityAttributeValue VALUES (2, 'Kolor', '#AAAA00');
INSERT INTO EntityAttributeValue VALUES (2, 'Cena', 1599.00);

Obiekt podstawowy nie ma, poza nazwą, żadnych atrybutów. Wszystkie atrybuty przeniesione są do tabeli EntityAttributeValue. Pole przechowujące wartość atrybutu jest typu sql_variant

Gdybyśmy zechcieli teraz pobrać wszystkie dane wykorzystując zwykłe złączenie zewnętrzne lewostronne, zrobilibyśmy to pewnie tak:

SELECT * FROM SomeItem
LEFT JOIN EntityAttributeValue ON ID=ParentID

Otrzymalibyśmy nieco rozstrzelone definicje wszystkich obiektów. Identyfikator obiektu wraz z nazwą pojawiałby się w zbiorze wynikowym tyle razy, ile atrybutów dany obiekt posiada. Dla aplikacji klienckich interpretacja takich zbiorów danych nie stanowiłaby żadnego problemu, ale z punktu widzenia bazy danych można powiedzieć, i byłoby to stwierdzenie prawdziwe, że dane nie są znormalizowane. Popatrzmy na wynik powyższego zapytania:

ID	Name	ParentID	Property	Value
1	Rower	1	Kolor	#FF0000
1	Rower	1	Data produkcji	01-01-2010
1	Rower	1	Cena	989.99
2	Komputer	2	Kolor	#AAAA00
2	Komputer	2	Cena	1599.00

Rower ma trzy atrybuty, komputer ma dwa atrybuty. Niby wszystko jasne, ale tylko dlatego, że tych rekordów jest mało. Większość osób pracujących z bazami danych woli jednak przeglądać wyniki w postaci tabelarycznej.

Przestawianie danych operatorem PIVOT

Aby nieco znormalizować dane można, a nawet, w niektórych przypadkach, wypadałoby przerzucić dane z wierszy do odpowiednich kolumn. To idealne zadanie dla instrukcji PIVOT:

SELECT Name, Kolor, [Data produkcji], Cena FROM EntityAttributeValue
PIVOT (
MAX(Value)
FOR Property IN ([Kolor], [Data produkcji], [Cena])
) P
JOIN SomeItem I ON ParentID=ID

Tak prosta operacja pozwala uzyskać miły dla oka rezultat w postaci zwykłej, tradycyjnej tabeli:

Name	Kolor	Data produkcji	Cena
Rower	#FF0000	01-01-2010	989.99
Komputer	#AAAA00	NULL	1599.00

Instrukcja PIVOT nie jest może najczęściej wykorzystywaną instrukcją, ale pokazuje swoje piękne oblicze tam, gdzie inne instrukcje zawodzą lub stają się nieznośne w użyciu. Przestawianie wartości w modelu Entity Attribute Value to jedno z tych miejsc. PIVOT jest pełnoprawną instrukcją SQL SERVER (także Oracle) - zwraca zbiór danych, który może być filtrowany, grupowany, łączony z kolejnymi tabelami. Samo przestawianie może być realizowane na podzapytaniu lub wyrażeniu tablicowym. Pełne poznanie instrukcji wykracza poza ramy tego wpisu. Co ważne, oczywiście w ramach tego wpisu, kolumny poddawane przestawianiu mogą być wygenerowane dynamicznie i przekazane w postaci dynamicznego zapytania SQL.

Zacząłem dynamicznie i skończyłem dynamicznie. Niektóre części artykułu postaram się rozwinąć w przyszłości (PIVOT, typ sql_variant), a do tego czasu zachęcam do zgłaszania uwag i sugestii za pomocą formularza kontaktowego, który pojawi się, a jakże - dynamicznie, po kliknięciu w pole Dodaj komentarz.

Kategoria:SQL Server

Paweł Dyl, 2013-12-20

Spis treści: