Relacyjna baza danych opisuje swoim modelem pewien fragment rzeczywistości, niezbędny do realizacji określonych celów dla których została stworzona.

Artykuł ten, przedstawia ogólne zasady projektowania bazy danych. W szczególności wyjaśnia czym jest normalizacja, w kontekście pisania zapytań łączących dane z wielu tabel. Opisuje najważniejsze (trzy pierwsze) postacie normalne – postulaty E. F. Codd’a.

Skrypt obrazujący, poniższy proces normalizacji – do pobrania tutaj (wersja dla SQL Server).

---

Problemy związane ze składowaniem danych

Model relacyjnych baz danych, opracowany przez Edgara F. Codd’a i przedstawiony w „A Relational Model of Data for Large Shared Data Banks”, definiuje m.in. cechy i strukturę dobrze zaprojektowanej bazy. Najłatwiej streścić sedno i przedyskutować potencjalne zagrożenia (złego projektu) na przykładzie, prezentującym kroki procesu normalizacji.

Po co właściwie wiele tabel, relacji między nimi. Czy nie prościej i łatwiej byłoby wszystko przechowywać w jednym miejscu – np. w jednej tabeli, tak jak w arkuszu Excel ?
Przyjrzyjmy się strukturze tabeli zawierającej informacje o zleceniach i Klientach.

SELECT * 
FROM #Zamowienia_UNF

Zwróć uwagę na (najważniejsze) problemy, których dostarczy nam tak zaprojektowana tabela :

• te same informacje przechowywane są wiele razy w wielu wierszach (np. AdresKlienta ). Zajmują one niepotrzebnie zasoby. Nie tylko dyskowe ! Ponieważ każdy wiersz, zawiera komplet informacji, zmniejsza się efektywność składowania danych w tabeli (jest to związane z anatomią pliku danych, sposobem zapisu wierszy). Skutkuje to w dalszej kolejności zwiększeniem ilości operacji odczytu /zapisu (kolejka do I/O to jedno z najczęstszych wąskich gardeł) a także późniejszą alokacją większych zasobów pamięci RAM (dla zdublowanych informacji).
• powtarzające się danej to także problem związany z aktualizacją. Jeśli chcemy poprawić np. adres Klienta, trzeba zmodyfikować wszystkie jego wystąpienia (może to być wiele rekordów). Co jeśli o którymś zapomnimy? Które dane będą prawdziwe? Pojawia się tu problem zachowania integralności danych. Problem aktualizacji eskaluje się gdy te same dane będą przechowywane w kilku tabelach. Nie dość, że trzeba pamiętać o modyfikowaniu wszystkich miejsc, to przecież wpływa to bezpośrednio na czas wykonywanych aktualizacji (transakcji). Zwiększa się prawdopodobieństwo wystąpienia zakleszczeń (deadlocków), następuje degradacja równoległego dostępu do danych. Jest to bardzo namacalny problem, subiektywnego postrzegania szybkości i jakości pracy systemu przez użytkowników.
• kolumny AdresKlienta oraz SzczegolyZamowienia, zwierają kolekcje wartości. Skutkuje to brakiem możliwości wykonania podstawowych operacji na danych – np. podsumowania według wartości zleceń, liczby pozycji etc. Ponadto przeszukiwanie takich kolekcji jest nieefektywne. Sprowadza się do przeszukiwania ciągów tekstowych (stringów). Brak możliwości zapewnienia pełnej integralności danych bo nie ma nad nimi kontroli.
• usunięcie rekordu – pozycji zamówienia, skutkuje utratą informacji o Kliencie, czyli takiej której nie chcielibyśmy tracić.
• analogicznie w drugą stronę – problem z dodawaniem informacji – konieczne określenie informacji, których być może jeszcze nie znamy, lub których może w ogóle nigdy nie będzie – np. Klient, który nie złożył żadnych zamówień.

---

Normalizacja

Wszystkie powyższe problemy i anomalie, rozwiązują odpowiednie postacie normalne (postulaty Codd’a). Postacie normalne wyższego rzędu, implikują wszystkie niższe. Baza danych jest znormalizowana np. do trzeciej postaci normalnej, jeśli są spełnione 1NF – 3NF.

Normalizacja to bezstratny proces organizowania danych w tabelach mający na celu zmniejszenie ilości danych składowanych w bazie oraz wyeliminowanie potencjalnych anomalii opisanych powyżej.

Pierwsza postać normalna 1NF

Pierwsza postać normalna to podstawa baz – mówi o atomowości danych. Czyli tabela (encja) przechowuje dane w sposób atomowy. Każde pole przechowuje jedną informację, dzięki czemu możemy dokonywać efektywnych zapytań. Wprowadza także pojęcie istnienie klucza głównego identyfikującego bezpośrednio każdy wiersz –unikalności. Warto pamiętać również o tym, że w dziedzinie teorii zbiorów kolejność wierszy jest dowolna (chyba że jawnie taki zbiór posortujemy).

Przejście na 1NF, nie może powodować utraty żadnych informacji, nie ma znaczenia kolejność elementów w zbiorze. Ta zasada dotyczy każdej postaci normlanej.

Mówimy, że tabela (encja) jest w pierwszej postaci normalnej, kiedy wiersz przechowuje informacje o pojedynczym obiekcie, nie zawiera kolekcji, posiada klucz główny (kolumnę lub grupę kolumn jednoznacznie identyfikujących go w zbiorze) a dane są atomowe. Zobaczmy, jak będzie wyglądała nasza struktura, jeśli spróbujemy doprowadzić ją do 1NF :

-- tabela w pierwszej postaci normalnej 
SELECT * 
FROM #Zamowienia_1NF

Projekt bazy danych powinien uwzględniać wszystkie wymogi biznesowe, obecne i przyszłe. Nie ma jednej recepty na najlepszy design.

Zwróć uwagę, że kolumna Adres nie jest atomowa. Wszystko tak naprawdę zależy od definicji atomowości, jaka nas satysfakcjonuje. Jeśli atomowa informacja o adresie to dla nas nazwa ulicy z numerem to wszystko jest ok. Co innego jeśli wymogi biznesowe potrzebują rozdzielenia tych informacji – np. system zleceń dla firmy kurierskiej. Obsługa jednej ulicy może być realizowana przez kilku kurierów (np. długa ulica Piotrkowska w Łodzi) – tu z pewnością trzeba by rozdzielić te informacje na dwie kolumny.

W tym miejscu, trzeba dodać jeszcze jedną istotną uwagę. Jeśli normalizujemy tabele, czyli rozdzielamy informacje na dane atomowe, konieczne jest określenie typu danych kolumn.

Stosujemy zawsze najmniejsze typy z możliwych, jakie są konieczne do spełnienia wymogów projektowych. Jeśli składujesz dane np. o dacie urodzin – interesuje nas za zwyczaj tylko data, nic więcej (chyba że w bazie szpitalnej, gdzie również interesująca jest informacja o godzinie narodzin). W większości przypadków, wybrany powinien zostać typ danych date – który zajmuje 3 bajty. Powszechnie popełnianym błędem jest nadużywanie dużych typów. Jeśli zostanie wybrany typ datetime – dla każdego rekordu, dla tej kolumny zostanie skonsumowane 8 bajtów. Niby niewiele, ale czy na pewno ?

• składowanie – różnicę (8-3=5 B) można pomnożyć przez np. N-milionów wystąpień (rekordów) – wtedy zaczyna robić wrażenie.
• pamięć RAM – podczas joinów tabel zamiast 3+3 czyli 6 bajtów na każdy łączony wiersz – zostanie zalokowane 8+8 = 16 B (10 B więcej, znów możemy rozpatrzeć to razy N-milionów wystąpień)
• zwiększony ruch sieciowy, obciążenie interfejsów kart sieciowych,
• zmniejszenie efektywności przechowywania wierszy na stronach – więcej operacji I/O
• a przecież takich kolumn z nadużyciem możemy mieć wiele w tabeli.

Pamiętajmy o tym podczas procesu projektowania czy też normalizacji tabel, że w dobry projekt bazy zaczyna się od fundamentu – czyli typów danych opisujących obiekty.

Druga postać normalna 2NF

Ta postać określa esencję dobrego projektowania bazy. Mówi o tym, że każda tabela powinna przechowywać dane dotyczące tylko konkretnej klasy obiektów.
Jeśli mówimy o encji (tabeli) np. Klienci, to wszystkie kolumny opisujące elementy takiej encji, powinny dotyczyć Klientów a nie jakiś innych obiektów (np. ich zamówień).

Zatem normalizując do 2NF, wydzielić należy zbiór atrybutów (kolumn) który jest zależny tylko od klucza głównego. Wszystkie atrybuty informacyjne (nie należące do klucza), muszą zawierać informacje o elementach tej konkretnej klasy (encji, tabeli) a nie żadnej innej. Kolumny opisujące inne obiekty, powinny trafić do właściwych encji (tabel) w których te obiekty będziemy przechowywać.

W naszym przykładzie wykonajmy analizę bazy zamówień, która jest już w 1NF. Oznaczmy na początek atrybuty informacyjne które nie należą do klasy Zamówień (nie zależą funkcyjnie od klucza głównego).

-- tabela w pierwszej postaci normalnej 
SELECT * 
FROM #Zamowienia_1NF

Widać że istnieją w niej atrybuty związane z różnymi obiektami. Najbardziej rzucającymi się w oczy są z pewnością kolumny opisujące klasę Klientów – zaznaczone na niebiesko. Następnie można wydzielić również informacje o detalach zamówienia (ElementZamowienia, Ilosc, CenaJedn) – w pomarańczowej ramce.

Te wszystkie atrybuty, muszą powędrować do nowych tabeli – właściwych dla obiektów danego typu.
Finalny obraz 2NF będzie składał się więc z 4 tabel : Zamowienia, Klienci, DetaleZamowien i Produkty.
W wyniku tej operacji, tabela z informacjami o zamówieniach będzie wyglądała tak :

-- tabela w drugiej postaci normalnej 
SELECT * 
FROM #Zamowienia_2NF

Nowe tabele będą wyglądały następująco :

-- nowa tabela przechowujące informacje o obiektach typu Klient
SELECT * 
FROM #Klient_2NF

-- nowa tabela przechowujące informacje o detalach zamówień
SELECT * 
FROM #DetaleZamowien_2NF

-- nowa tabela przechowujące informacje o produktach
SELECT * 
FROM #Produkty_2NF

Jak widać usunięcie redundantnych informacji, skutkuje wzrostem złożoności struktury. Nie dość, że tworzymy nowe tabele, to jeszcze dokładamy kolumny np. IDKlient. Jednak, uwierz mi – nie jest to duża strata, zważywszy na zysk jaki nam daje ten krok normalizacji per saldo.

Relacyjne systemy bazy danych są projektowane do działania na zbiorach. Owszem koszt łączenia tabel bywa wysoki, ale zapewniając odpowiednie indeksy na łączonych kolumnach, jesteśmy w stanie zagwarantować równowagę pomiędzy wydajnością a plusami wynikającymi z normalizacji.

Doprowadziliśmy więc do sytuacji, w której każda tabela (encja) przechowuje informacje opisujące tylko obiekty właściwe dla niej. Cały proces, musi być bezstratny – udowodnijmy to poniższym zapytaniem :

SELECT z.NumerZamowienia, k.NazwaKlienta, k.Adres, k.KodPocztowy, k.Miasto, k.Wojewodztwo
       ,z.DataZamowienia, p.Nazwa, d.Ilosc, d.CenaJedn , z.WartZamNetto, z.Vat
       ,z.WartZamBrutto
FROM #Zamowienia_2NF AS z 
   INNER JOIN #Klient_2NF AS k ON z.IDKlient=k.IDKlient
   INNER JOIN #DetaleZamowien_2NF AS d ON z.NumerZamowienia=d.NumerZamowienia
   INNER JOIN #Produkty_2NF AS p ON d.KodProduktu=p.KodProduktu
ORDER BY z.NumerZamowienia

Trzecia postać normalna 3NF

Trzecia postać normalna głosi, że kolumna informacyjna nie należąca do klucza nie zależy też od innej kolumny informacyjnej, nie należącej do klucza. Czyli każdy niekluczowy argument jest bezpośrednio zależny tylko od klucza głównego a nie od innej kolumny.

W naszym przypadku widać, że kolumny informacyjne związane z kosztami danego zamówienia oraz stawką podatku VAT, są ze sobą skorelowane. Z jednej z nich można śmiało zrezygnować, nie tracąc żadnej informacji (każda z nich zależy od klucza głównego, ale również od pozostałych dotyczących wartości). Możemy wyznaczyć np. wartość brutto na podstawie stawki VAT i wartości netto itd..

SELECT NumerZamowienia,IDKlient,DataZamowienia,WartZamNetto,WartZamBrutto,
	((WartZamBrutto/WartZamNetto-1)*100) AS [Vat %]
FROM #Zamowienia_3NF

Innym przykładem, mogą być kolumny wyliczeniowe wartości netto/brutto itp. ). Łatwo jednak w takich przypadkach podać przykłady, świadomego łamania 3NF na rzecz wydajności. Chociażby skomplikowane wyliczenia które muszą być wykonywane dla każdego wiersza, przy każdym zapytaniu.

Zalety i wady normalizacji

Projektowanie baz danych z pewnością nie jest trywialne. Pierwszym krokiem, powinna być właściwa analiza wymagań biznesowych, uwzględniająca przyszłe potrzebny i możliwości skalowania projektu.

Konieczne jest świadomość istnienia potencjalnych zagrożeń i wyczucie, wynikające z pewnością z doświadczenia – aby odpowiedzieć na pytanie jak mocno normalizować tabele. Najczęściej spotykany poziom to 3 postać normalna. Są sytuacje, zresztą nasuwające się intuicyjnie, w których zależy nam na procesie odwrotnym – denormalizacji celem maksymalnego zwiększenia szybkości wykonywania zapytań.

Poniżej kilka istotnych cech związanych z normalizowaniem baz danych :

• zmniejszamy ogólną liczbę danych przechowywanych w bazach
• rozwiązujemy problemy anomalii dodawania, modyfikacji i usuwania informacji (rekordów) z bazy
• czasem spowalniamy wykonywanie zapytań – cena jaką płacimy za konieczność łączenia tabel.
• ale też przyspieszamy wykonywanie określonych zapytań – tworzymy osobne tabele więc możemy utworzyć więcej indeksów klastrowych, lepsza efektywność przechowywania danych w tabelach.
• wąskie tabele to bardziej efektywne przetwarzanie i składowanie ich na dysku, mniej operacji I/O.
• lepsze zarządzanie transakcjami – szybsze wykonywanie update’ów, mniej blokad.

Oprócz przedstawionych powyżej trzech pierwszych postaci normalnych, spotkać możemy wyższe formy, choć zazwyczaj spotykane jedynie w teoretycznych rozważaniach :

• postać normalna Boyce-Codd’a (określana również mianem 3.5NF)
• czwarta postać normalna 4NF
• piąta postać normalna 5NF