o modelu relacyjnym i złączeniach w odzieżowej pigułce

Moim zdaniem kwintesencją baz danych są właśnie złączenia. Możliwość łączenia ze sobą różnych danych w logiczną całość. By zrozumieć złączenia trzeba zrozumieć logikę relacyjnych baz danych. W tej tematyce powstało wiele książek opartych o wizję w sumie jednego człowieka (Mr. Codd). Jeśli by spojrzeć na temat relacyjności w sposób dość uproszczony tak by przedstawić to w maksymalnej pigułce należy przekazać min. następujące informacje:

zbiór danych – przysłowiowy worek – nazywany dumnie encją lub po prostu tabelą – jest układem n x m.
Mamy n kolumn i m wierszy. Kolumny są poddane pewniej normalizacji. Ze względów praktycznych w danej kolumnie przechowuje się dane jednego rodzaju (typu) i wykorzystywane do tych samych rzeczy. W relacyjnym modelu wartość wiersza w kolumnie powinna być informacją atomową (czyli nierozbijalną). Patrząc od strony wierszy na tabelę musimy uzyskać ich (wierszy) niepowtarzalność.

Zostając chwilę przy niepowtarzalności danych należy spojrzeć na temat trochę bardziej globalnie. Jeśli weźmiemy pod uwagę wszystkie nasze zbiory w obrębie jednej bazy powinniśmy dążyć do minimalizowania powtórzeń. Ta minimalizacja powtórzeń ma swoją fachową nazwę pt. normalizacja. Do tej pory wymyślono kilka postaci normalizowanych z czego 3 są powszechnie używane. Możemy na normalizacje spojrzeć jak na pewnego rodzaju skalę gdzie 1 w obecnych standardach systemów transakcyjnych oznacza porażkę, a 3 jest oceną przesadną (niby od przybytku głowa nie boli, ale co za dużo to nie zdrowo). Większość systemów transakcyjnych, czyli użytkowych ocenia się na przysłowiowe 2,5. O normalizacji kiedyś napiszę osobną notkę, bo jest to dość głęboka sprawa.

Z powyższego akapitu należy zapamiętać iż w relacyjnych bazach danych staramy się dążyć do niepowtarzalności danych.

Takie myślenie wymusza na nas pewną logikę działania.

Jeśli posiadamy logiczne zbiory np. przyjaciół, klientów, skarpetek to rzeczy wrzucone do takiego worka mają jakieś wspólne cechy, po których przez nadanie im konkretnych wartości możemy zdefiniować iż jest to jakiś konkretny przedmiot (np. skarpetka).
Skarpetki są dość fajnym przykładem, z tego względu iż trzymamy je razem w jednej szufladzie, kartonie, skrzynce.
Patrząc na szufladę ze skarpetkami możemy im przypisać pewne właściwości: kolor, rozmiar, materiał, współczynnik zużycia ;-), lewa, prawa.

Definicja wspomnianych atrybutów definiuje nam konkretną parę skarpetek lub konkretną skarpetkę. No chyba, że kupiliśmy w makro zgrzewkę takich samych. A załóżmy, że tak się stało. Podpiszmy skarpetki markerem.
Napis stanie się naszym kluczem unikalnym. Kluczem głównym, czyli to co jednoznacznie określa nam że ta skarpetka w mojej ręce to jest właśnie ta. Jeśli mamy same białe skarpetki to prosząc kogoś o podanie białej skarpetki mamy wysoce dużą szanse iż nie otrzymamy tej, którą mieliśmy na myśli.

Tak samo mamy w bazie danych, gdzie każdy przedmiot w worku ma swoje atrybuty i numer, który go jednoznacznie identyfikuje. Oczywiście da się odejść od tej reguły (tylko po co?). Możemy się przy okazji dopuścić pewnej dawki filozofii, czy każda skarpetka powinna mieć swój nr? czy tylko każda para? W bazie danych powinniśmy każdą skarpetkę oznaczyć osobno i nadać jej atrybut z oznaczeniem pary. W ten sposób możemy sięgnąć konkretną skarpetkę z szufladki jak i od razu całą parę.

Trochę daleko nam od tematu złączeń, ale na przykładzie skarpetek w naszej wyobraźni możemy utworzyć worek z majtkami, stanikami, spódnicami i koszulkami.

Kompletując ubiór, zwłaszcza Panie zwrócą uwagę na kolorystykę. Zakładając dość częstą poranną sytuację pt w co mam się ubrać. Przeciętna kobieta chciała by uzyskać zbiory ubrań, które do siebie pasują. A że baza danych lubi odpowiadać na dobrze zadane pytania, z chęcią zwróci nam odpowiedź.

Pytając w co mam się ubrać
Otrzymamy dość żartobliwą odpowiedź, możesz założyć wszystko co masz w szafach.

select * from majtki,skarpetki,koszulki,spodnice,staniki;

To co otrzymaliśmy nazywa się w niektórych mniej humanistycznych kręgach iloczynem kartezjańskim. Czyli każdy element został powiązany z każdym z innego worka.
Po 2 rzeczy w każdym worku dostaniemy 2 do potęgi 5 (worków) wyników (dla czepialskich skarpetki są zwinięte razem i parę traktuję jako jedną rzecz). Taka forma pobierania danych nie jest zbyt szczęśliwa i nie jest raczej stosowana poza przypadkami gdy chcemy wygenerować wszystkie możliwe scenariusze.

Frustrując się nieco możemy zadać kolejne pytanie.

Co mogę ubrać białego ?

select * from majtki,skarpetki,koszulki,spodnice,staniki
where majtki_kolor = 'bialy' and skarpetki_kolor='bialy'
and koszulki_kolor = 'bialy' and spodnice_kolor= 'bialy'
and stanik_kolor='bialy'

Nie znam się aż tak na kobiecych garderobach, ale wydaje mi się że białe spódnice nie są zbyt popularne.
Jeśli więc nie posiadamy białej spódnicy to baza odpowie, że nie znalazła kompletu, który by spełniał pokładane oczekiwania. Możemy dopuścić się pewnej modyfikacji:

select * from majtki,skarpetki,koszulki,spodnice,staniki
where majtki_kolor = 'bialy' and skarpetki_kolor='bialy'
and koszulki_kolor = 'bialy' and spodnice_kolor in ('bialy','czarny','szary')
and stanik_kolor='bialy';

w ten sposób otrzymamy iloczyn kartezjański wszystkich białych rzeczy dorzucając czarne i szare spódnice. Jest w czym wybierać. Domyślam się iż kobiet to nadal nie zadowoli.

Zostawiając wybór całościowy, możemy na chwilę skupić się na mniejszym problemie. Można by pomyśleć o bieliźnie. Załóżmy, że omawiana kobieta kupuje markową bieliznę w kompletach. Nasze komplety leżą w osobnych workach, ale posiadają identyfikator kompletu.

select * from majtki,staniki
where majtki_komplet= statniki_komplet;

W wyniku otrzymamy bieliznę z tych samych kompletów. Na tym etapie warto by wprowadzić szerszy bardziej czytelny zapis:

select *from majtki
inner join staniki on
staniki_komplet = majtki_komplet

Osobiście najbardziej preferuję tą formę zapisu jednak dla większości RDBMS nie robi to żadnej różnicy.
Istnieje jeszcze zapis pośredni:

select * from majtki
join staniki on
staniki_komplet = majtki_komplet

Dla czytelności kodu lepiej jednak stosować mój ulubiony zapis.
Łączenia, które przedstawiłem nazywają się ogólnie złączeniami wewnętrznymi.
W powyższym przykładzie z majtkami i stanikami, jeśli założymy że numerujemy z jednej puli komplety i za komplet uważamy także rzeczy kupione pojedynczo, to w wyniku zapytania znikną nam wszystkie rzeczy bez kompletu. Można by też w ogóle nie oznaczać rzeczy niekompletowych, tylko wtedy albo byśmy uzyskali jeden wielki komplet np. z numerem 0 ;-), albo wartości null, które są przeze mnie bardzo nielubiane, bo w pewnych sytuacjach mogą niekorzystnie zmieniać nam wyniki zapytania. Na szczęście w większości RDBMS (nie znam wszystkich) wartości null nie idzie do siebie przypisać, w sensie da się to zapisać ale operacja logiczna nie powinna zwrócić prawdy.

Skoro istnieją złączenia wewnętrzne to istnieją pewnie złączenia zewnętrzne.
Zewnętrzne złączenia są bardzo użyteczne (i drogie) pozwalają one pokazać niedopasowane rzeczy.

I teraz o ile:
select *from majtki
inner join staniki on
staniki_komplet = majtki_komplet

i

select *from staniki
inner join majtki on
staniki_komplet = majtki_komplet

niczym w wyniku się nie różni, o tyle w złączeniach zewnętrznych kolejność łączenia ma znaczenie dla wyniku końcowego.

Więc zakładając iż w pewnych sytuacjach kobieta jest skłonna zrezygnować ze stanika możemy wytworzyć takie zapytanko:

select * from majtki
left outer join staniki on
staniki_komplet = majtki_komplet

zapytanie wyświetli nam listę wszystkich dostępnych majtek oraz dostawi próbując dopasować komplety staniki.
W miejscach gdzie nie powstało dopasowanie zostanie wyświetlona wartość null. Tak więc zobaczymy komplety plus zdekompletowane mówiąc kolokwialnie gacie.

Na obecnym etapie należy wspomnieć o możliwościach filtrowania zapytań łączonych.

Otóż

select * from majtki
inner join staniki on
majtki_komplet = staniki_komplet
where majtki_kolor = 'bialy'

jest dokładnie tym samym co:

select * from majtki
inner join staniki on
majtki_komplet = staniki_komplet
and majtki_kolor = 'bialy'

przynajmniej dla Oracle (przetestowane). Przy bardziej rozbudowanych zapytaniach i wielu złączeniach w MSSQL 2005 przerzucenie warunku joinowania pomiędzy inne tabele potrafi bardzo niekorzystnie wpłynąć na efektywność zapytania, Oracle wydaje się być na to odporne (ale muszę to potwierdzić).

Oczywistym powinno być iż złączenia są najefektywniejsze gdy są wykonywane na kolumnach objętych indeksami.
Ale sama indeksacja i optymalizacja jest osobnym tematem.

Przykład o którym mówię:

select * from tab1 t1
inner join tab2 t2 on
t1.id = t2.id
inner join tab3 t3 on
t2.id_f = t3.id
inner join tab4 t4 on
t3.id_f = t4.id
and t1.id_d = t2.id_d -- ta linia powinna być w pierwszym joinie.

takie zapytanie jest bardzo niekorzystne. Warunek oznaczony komentarzem powinien znaleźć się w pierwszym joinie. Napisałem że Oracle wydaje się być na to odporny. Wyznaczając plany dla różnych zapytań nie widać różnicy, jednak nie operowałem od dawna na przykładzie, który mógłby wykazać zauważalną różnicę.

W przypadku złączeń zewnętrznych warunki joinowania są rozróżniane z filtrami

select * from majtki
left outer join staniki on
majtki_komplet = staniki_komplet
where staniki_komplet is null

jest czymś innym niż

select * from majtki
left outer join staniki on
majtki_komplet = staniki_komplet
and staniki_komplet is null

pierwsze zapytanie zostanie wyfiltrowane do wierszy bez złączenia. A drugi przypadek zostanie potraktowany jako warunek łączenia i pokaże wiersze dla których staniki_komplet mają wartość null przed joinowaniem.

Napisałem też ze złączenia zewnętrzne są drogie. Ich koszt jest relatywnie większy (czasem nawet o setki procent) w stosunku do złączeń wewnętrznych. Dlatego warto przemyśleć strukturę zapytania by uniknąć złączeń zewnętrznych. O ile na etapie pobierania danych często dość trudno coś wykreować o tyle w trakcie definiowania struktury bazy warto zwrócić na to uwagę.

Istnieje także right outer join, który jest odwrotnym zestawieniem w stosunku do left outer join. Z moich obserwacji jest on mniej wydajny niż left outer join. Jeśli jestem zmuszony korzystać ze złączeń zewnętrznych staram się konstruować tak zapytanie by używać left outer join.
Right outer ma także dużą tendencję do generowania dużych wyników. Dla osób bez wprawy niezalecane jest pisanie takich zapytań na bazach transakcyjnych produkcyjnych (nie zalecane przeze mnie).

Współczesne bazy nie posiadają raczej rażących ograniczeń w ilości joinów.

Czego nie można powiedzieć o łączeniach typu union. Unie to łączenie wyników dwóch zapytań o takiej samej ilości kolumn. Dodatkowym ograniczeniem unii jest fakt iż kolumny muszą być tego samego typu.

select 'majtki',majtki_kolor from majtki
union all
select 'staniki',staniki_kolor from majtki

powyższy przykład jest mega trywialny, ale obrazuje zasadę działania. W jednym zapytaniu możemy mieć maksymalnie 255 unii (w MSSQL i ORACLE). Dodatkowo Unie uchodzą za bardzo kosztowne dla optymizerów.
I w wielu wypadkach da się obejść bez nich. Istnieje także rozwiązanie polegające na umieszczaniu wyników zapytań w tabelach tymczasowych.

Samych złączeń jest wiele więcej, opisałem najważniejsze i najczęściej wykorzystywane. Zrozumienie zagadnień związanych z opisanymi złączeniami oraz z wiedzą dotyczącą grupowania pozwala budować zapytania, które mogą być działającymi i efektywnymi raportami.

Na koniec należy się kilka słów o modelu relacyjnym. Skoro możemy efektywnie łączyć dane możemy je rozbijać by unikać ich powtarzalności. Idealnym przykładem jest zapis faktury w RDBMS.
Gdyby nie normalizować zapisu w każdym wierszu tabeli posiadalibyśmy wszystkie informacje dotyczące kontrahenta, konkretnej pozycji itd. Taki zapis jest opisany jako 1NF (1 stopień normalizacji). W takim wypadku nie ma potrzeby złączeń.
Bardziej efektywnym zapisem jest rozróżnienie danych na informacje o nagłówku i pozycjach. Mamy dwie osobne tabele powiązane identyfikatorem faktury. Dokonując złączenia uzyskamy wynik jak w 1NF. Taką sytuację nazywamy 2NF (2 stopień normalizacji). Tworząc kolejne relacje np. do tabeli z informacjami o towarach na fakturze oraz informacjami związanymi z kontrahentami otrzymamy 3NF (najbardziej pożądany przez teoretyków 3 stopień normalizacji).

Jednak teraz trzeba zadać sobie pytanie: Co się stanie jak klient zmieni swoje dane w 3NF ? Np. się przeprowadzi?
Wtedy aktualizując dane na karcie kontrahenta zmienimy zapis faktury, dlatego też napisałem że 3NF niekoniecznie jest najlepszym rozwiązaniem.

Leave a Reply

Your email address will not be published. Required fields are marked *

20 + 3 =