Spis treści:

Kategoria:SQL Server


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:

SELECTFROM 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:

IDNameParentIDPropertyValue
1Rower1Kolor#FF0000
1Rower1Data produkcji01-01-2010
1Rower1Cena989.99
2Komputer2Kolor#AAAA00
2Komputer2Cena1599.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:

NameKolorData produkcjiCena
Rower#FF000001-01-2010989.99
Komputer#AAAA00NULL1599.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

, 2013-12-20

Brak komentarzy - bądź pierwszy

Dodaj komentarz
Wyślij
Ostatnie komentarze
To samo pytanie co wyżej. Mam za zadanie dodać kolumnę do istniejącej tabeli łącząc obie inne kolumny ze sobą, ale nie mam pojęcia jak za to się zabrać
działa :) tylko była literówka :)
Podziękował. Trochę późno, po 8 latach, ale dzięki za testy (rozumiem że dla SQL2012 robione). Tak się właśnie zastanawiałem ile złego czynię stosując czasem __(max).
Super robota, korzystając z innych internetowych kalkulatorow po prostu wątpiłem w ich prawdomówność, w końcu trafiłem tutaj i wynik w końcu jest wiarygodny. 40 km w 2 h 810 kcal, ciekawostka: na fitatu wyliczyło mi 5700 kcal 😊 najlepiej będzie chyba jak kupię zegarek sportowy.
Wielkie dzieki za solidne wyjasnienia tematu.