SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Projektowanie i normalizacja bazy danych

Relacyjnadesign_small 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 Codd’a.

Skrypt obrazujący, poniższy proces normalizacji – do pobrania tutaj.


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

Normalizacja bazy
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 AdresZamowienia 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

Pierwsza postać normalna
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

Normalizacja do 2NF
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

NormalForm_03

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

-- nowa tabela przechowujące informacje o obiektach typu Klient
select * from #Klient_2NF

NormalForm_04

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

NormalForm_05

-- nowa tabela przechowujące informacje o produktach
select * from #Produkty_2NF

NormalForm_06
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, f.Ilosc, d.CenaJedn , z.WartZamNetto, z.Vat , z.WartZamBrutto
 
FROM #Zamowienia_2NF z inner join #Klient_2NF k on z.IDKlient=k.IDKlient
	inner join #DetaleZamowien_2NF d on z.NumerZamowienia=d.NumerZamowienia
	inner join #Produkty_2NF p on d.KodProduktu=p.KodProduktu
OREDER BY 1

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

NormalForm_08
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)
  • czarta postać normalna 4FN
  • piąta postać normalna 5FN

9 thoughts on “Projektowanie i normalizacja bazy danych

  1. Przeczytałem chyba z 10 opracować nt postaci normalnych i jesteś pierwszą osobą która zrobiła to w naprawdę zrozumiały sposób :) Dzięki

  2. Mam pytanie do 2NF. W artykule jest napisane, że z tabeli powinny być usunięte atrybuty, które nie opisują bezpośrednio encji Zamówienie. Rozumiem o co chodzi bo to jakby opisuje praktycznie rozumienie 2NF.

    Jak przeanalizować tabele Zamówienia pod kątek funkcyjnej zależności od klucza głównego ? W szczególności interesuje mnie stwierdzenie (z np wikipedii), że z tabeli powinny być usunięte atrybuty, które są funkcyjnie powiązane z częścią klucza głównego. W tabeli Zamowienie kluczem glownym jest kolumna „nr_pozycji” (?). W takim wypadku każda kolumna, którą usuwamy z tabeli Zamówienie jest funkcyjnie zależna od całego klucza głównego (dla każdej unikatowej wartości nr_pozycji mamy dokladnie jedna wartość danego atrybutu) więc nie powinna zostać usunięta żadna kolumna…

    Wiem, że którąś część teoretycznej definicji źle rozumiem lub biorę zbyt dosłownie tylko nie wiem którą…

    • W tabeli „Zamówienia” kluczem głównym powinna być raczej kolumna „NumerZamówienia”, bo to ona jednoznacznie określa dane zamówienie. Wtedy z tej tabeli usuwamy wszystkie obiekty które nie odnoszą się do tego klucza, czyli informacje o produktach i klientach. Pozostają w niej jedynie informacje o samych zamówieniach, które odnoszą się do całego klucza głównego, w tym wypadku prostego, bo założonego na pojedynczej kolumnie „NumerZamówienia”.
      Drugą postać normalną chyba łatwiej wytłumaczyć na przykładzie tabeli, która ma założony klucz główny na kilku kolumnach. Na przykład tabela „Osoby” z kluczem głównym złożonym, obejmującym dwie kolumny: „Imię” i „Nazwisko”. Wtedy kolejna kolumna, np. „Pesel” musi odnosić się do całości tego klucza, czyli i do imienia i do nazwiska. Zatem kolumna „Pesel” pozostanie w tabeli „Osoby”, zgodnie z 2 postacią normalną. Ale gdyby w takiej tabeli istniała kolumna z jakimiś innymi numerami, skojarzona tylko z imieniem czyli tylko z częścią klucza głównego, wtedy musiałby zostać przeniesiona do innej tabeli, zawierającej jakieś dodatkowe numery przypisane do poszczególnych osób. Tak to chyba wygląda, jeżeli coś źle napisałem to bardzo proszę o sprostowanie mojej wypowiedzi.

  3. pytania odnośnie: Druga postać normalna 2NF

    Tabela #Klient_2NF:
    Czy, aby 2NF była spełniona, to nie należałoby kolumn Miasto i Województwo wyciągnąć do jeszcze jednej tabeli, w której kluczem głównym byłby KodPocztowy?
    (A żeby było jeszcze lepiej to utworzyć oddzielne tabele dla miast, województw i kodów pocztowych – jedno województwo może mieć wiele miast, jedno miasto wiele kodów; kod jednoznacznie identyfikuje miasto i województwo)

    Tabela #DetaleZamowien_2NF:
    Czy kolumna CenaJedn nie jest zbędna w tej tabeli i nie powinna istnieć jedynie w tabeli #Produkty_2NF?

    • To przykład, warto by było rozważyć utworzenie tabeli z adresami bo Klient często ma dwa lub nawet więcej adresów (np zamieszkania i wysyłki) – wszystko zależy od scenariusza. Więc tak naprawdę cały adres powinien być w innej tabeli. Odnośnie drugiego pytania to w tym scenariuszu cena jednostkowa to ta która jest na fakturze (czyli zakładamy że cena produktu może zmieniać się w czasie). Dobrze jest więc wiedzieć jaką cenę zapłacił klient np. rok temu. W tym artykule opisuję pewne ogólne podejście do normalizacji. Nie jest to w żadnym wypadku idealny scenariusz, bo taki poprostu nie isnieje… każdy system może mieć inne wymagania.

Leave a comment

Twój adres email nie zostanie opublikowany.

Uzupełnij równanie (SPAM protection) *