Normalizacja baz danych od 1NF do 3NF. Praktyczny poradnik

Jedna tabela z klientem, adresem, produktem i zamówieniem może na początku wyglądać wygodnie, ale szybko zaczyna generować duplikaty, błędy i trudne do wykrycia niespójności. Dobra normalizacja baz danych pomaga rozdzielić informacje tak, aby każda znalazła się w odpowiednim miejscu, a zmiana jednego faktu nie wymagała poprawiania dziesiątek rekordów. Pokażę, czym są postacie normalne, jak rozbić nieuporządkowaną tabelę oraz kiedy świadomie odejść od idealnie znormalizowanego modelu.

Po co w ogóle dzielić dane na wiele tabel

Normalizacja to sposób projektowania relacyjnej bazy, w którym dane dzieli się na logicznie powiązane tabele. Każda tabela powinna opisywać jeden główny typ obiektu albo jedno zdarzenie, a relacje między nimi wyznaczają klucze główne i obce.

Największy problem źle zaprojektowanej tabeli nie polega na tym, że wygląda nieestetycznie. Chodzi o powtarzanie tych samych informacji. Jeżeli adres klienta zapiszesz przy każdym zamówieniu, jedna zmiana może wymagać aktualizacji stu wierszy. Wystarczy, że jeden zostanie pominięty, a baza zacznie przechowywać sprzeczne dane.

Trzy anomalie, których warto unikać

• Anomalia aktualizacji powstaje, gdy tę samą informację trzeba zmienić w wielu rekordach, ale aktualizowany jest tylko jeden z nich.
• Anomalia dodawania pojawia się wtedy, gdy nie można zapisać nowego klienta lub produktu bez tworzenia sztucznego zamówienia.
• Anomalia usuwania występuje, gdy usunięcie ostatniego zamówienia powoduje również utratę danych o kliencie albo produkcie.

W praktyce właśnie te trzy sytuacje są dla mnie najlepszym testem jakości projektu. Jeśli jedna operacja biznesowa przypadkiem kasuje lub zmienia informacje należące do innego obiektu, schemat prawdopodobnie wymaga poprawy.

Podział tabel nie oznacza jednak automatycznie lepszego systemu. Zbyt daleko posunięte rozdrobnienie może utrudnić zapytania i zwiększyć liczbę połączeń między tabelami. Celem jest spójny model danych, a nie bicie rekordu w liczbie tabel.

Jak rozumieć 1NF, 2NF, 3NF i BCNF

Postacie normalne tworzą kolejne poziomy uporządkowania. Każda następna zakłada spełnienie wcześniejszej, dlatego nie da się sensownie oceniać 3NF bez sprawdzenia 1NF i 2NF.

Pierwsza postać normalna

W 1NF każda komórka przechowuje jedną logiczną wartość. Pole telefony zawierające tekst „501111222, 502333444” łamie tę zasadę, podobnie jak kolumny produkt_1, produkt_2 i produkt_3. Liczba elementów jest wtedy zaszyta w strukturze tabeli, więc dodanie czwartego produktu wymaga zmiany schematu.

Lepszym rozwiązaniem jest osobna tabela, na przykład numery_telefonów, z kluczem klienta i numerem telefonu. W ten sposób liczba telefonów może się zmieniać bez dodawania nowych kolumn.

Druga postać normalna

2NF ma znaczenie głównie wtedy, gdy tabela używa klucza złożonego, czyli takiego, który składa się z kilku kolumn. Załóżmy, że tabela pozycje_zamówień ma klucz (zamówienie_id, produkt_id). Nazwa produktu zależy wyłącznie od produkt_id, a nie od całej pary, więc powinna trafić do tabeli produktów.

Jeżeli tabela ma wyłącznie pojedynczy klucz, problem zależności częściowej nie występuje. To częsty szczegół pomijany w uproszczonych wyjaśnieniach 2NF.

Trzecia postać normalna

W 3NF atrybuty opisowe zależą bezpośrednio od klucza. Jeśli tabela pracownicy zawiera dział_id oraz nazwa_działu, to nazwa działu zależy od działu, a nie bezpośrednio od pracownika. Powinna więc znaleźć się w tabeli działy.

To właśnie 3NF jest zwykle rozsądnym celem dla aplikacji webowych. BCNF i wyższe postacie normalne przydają się w bardziej złożonych modelach, ale nie każdy projekt potrzebuje formalnego dochodzenia do najwyższego możliwego poziomu.

Przykład rozbicia tabeli zamówień

Wyobraźmy sobie początkowy projekt sklepu internetowego. Jedna tabela przechowuje identyfikator zamówienia, dane klienta, adres, produkt, kategorię, cenę i liczbę sztuk. Taki układ szybko powiela dane klienta i produktu przy każdym kolejnym zamówieniu.

W tym układzie adres e-mail jest własnością klienta, nazwa kategorii opisuje kategorię, a cena produktu nie jest częścią danych klienta. Wartości należące do różnych obiektów zostały wymieszane w jednym miejscu.

Docelowy podział

• klienci przechowuje dane osoby, takie jak imię, nazwisko i adres e-mail;
• adresy przechowuje adresy przypisane do klienta lub zamówienia, zależnie od reguł biznesowych;
• produkty zawiera nazwę, cenę i identyfikator kategorii;
• kategorie przechowuje nazwy kategorii;
• zamówienia opisuje samo zamówienie i wskazuje klienta;
• pozycje_zamówień łączy zamówienie z produktami oraz przechowuje liczbę sztuk.

Przykładowe definicje mogą wyglądać tak:

CREATE TABLE klienci (
    id INT PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    imie VARCHAR(100) NOT NULL,
    nazwisko VARCHAR(100) NOT NULL
);

CREATE TABLE produkty (
    id INT PRIMARY KEY,
    nazwa VARCHAR(200) NOT NULL,
    cena DECIMAL(10,2) NOT NULL,
    kategoria_id INT NOT NULL
);

CREATE TABLE pozycje_zamowien (
    zamowienie_id INT NOT NULL,
    produkt_id INT NOT NULL,
    liczba_sztuk INT NOT NULL,
    PRIMARY KEY (zamowienie_id, produkt_id)
);

Klucz obcy kategoria_id wskazuje rekord kategorii, a para zamowienie_id i produkt_id identyfikuje konkretną pozycję. Dzięki temu zmiana nazwy kategorii odbywa się w jednym miejscu, a baza może pilnować poprawności relacji za pomocą ograniczeń FOREIGN KEY.

Trzeba jednak uważać na cenę. Cena aktualna produktu i cena zapisana na historycznej pozycji zamówienia to dwie różne informacje. W zamówieniu warto zachować cenę z chwili zakupu, nawet jeśli tabela produktów zmieni ją później. Nie jest to błąd normalizacji, tylko świadome odwzorowanie reguły biznesowej.

Jak przeprowadzić normalizację krok po kroku

Nie zaczynam od mechanicznego przenoszenia kolumn między tabelami. Najpierw spisuję, jakie obiekty występują w systemie i jakie fakty opisują. W aplikacji rezerwacyjnej będą to na przykład użytkownicy, pokoje, rezerwacje i płatności, a nie jedna wielka tabela „wszystko”.

1. Wypisz encje, czyli główne obiekty biznesowe.
2. Ustal klucz główny każdej tabeli i sprawdź, czy jednoznacznie identyfikuje rekord.
3. Rozbij wartości złożone, takie jak pełny adres, lista telefonów lub wiele produktów w jednym polu.
4. Znajdź zależności funkcyjne. Pytaj, czy wartość jednej kolumny jednoznacznie wyznacza wartość innej.
5. Usuń zależności częściowe od fragmentu klucza złożonego.
6. Usuń zależności przechodnie, przenosząc dane opisujące inne obiekty do osobnych tabel.
7. Dodaj klucze obce, UNIQUE i NOT NULL, aby reguły modelu były pilnowane przez bazę, a nie tylko przez kod aplikacji.

Dobrym sprawdzianem jest prześledzenie typowych operacji. Czy można dodać produkt bez zamówienia? Czy da się usunąć jedną pozycję zamówienia bez utraty danych o produkcie? Czy aktualizacja adresu klienta wymaga zmiany jednego rekordu? Odpowiedzi szybko pokazują, gdzie projekt ma słabe punkty.

Na tym etapie przydaje się diagram ERD, czyli graficzny opis tabel, kluczy i relacji. Nie traktuję go jako dokumentacji tworzonej na koniec. Najwięcej błędów wychodzi właśnie wtedy, gdy model obejrzymy przed napisaniem pierwszego zapytania SQL.

Warto również testować dane przykładowe. Wstaw kilka produktów do jednego zamówienia, klienta z dwoma adresami, produkt bez sprzedaży i kategorię bez produktów. Jeśli któryś przypadek wymusza puste, sztuczne albo zduplikowane rekordy, struktura prawdopodobnie nie odzwierciedla dobrze reguł systemu.

Normalizacja a wydajność zapytań

Najczęściej powtarzany mit mówi, że znormalizowana baza zawsze działa szybciej. To zbyt proste. Mniejsza liczba duplikatów ogranicza rozmiar danych i ułatwia aktualizacje, ale odczyt informacji może wymagać kilku operacji JOIN.

W systemie transakcyjnym, takim jak sklep, bank albo panel użytkownika, spójność zwykle ma pierwszeństwo. W hurtowni danych lub widoku raportowym wygodniejsze może być częściowe powielenie danych, aby raporty wykonywały mniej połączeń.

Denormalizacja ma sens dopiero wtedy, gdy znamy konkretny problem wydajnościowy. Najpierw mierzę czas zapytań, sprawdzam plan wykonania i indeksy, a dopiero potem rozważam duplikowanie danych. Przenoszenie kolumn „na zapas” często tylko utrudnia późniejsze utrzymanie.

Jeżeli tabela jest często odczytywana, ale rzadko zmieniana, można rozważyć widok materializowany, dodatkową tabelę raportową albo cache. To nadal powinno być świadome odstępstwo z opisanym źródłem prawdy i sposobem synchronizacji.

Gdzie początkujący najczęściej popełniają błędy

Jedna tabela dla całej aplikacji

Wrzucenie danych użytkownika, płatności, produktów i logów do jednego miejsca wydaje się szybkie, ale miesza różne cykle życia danych. Zwykle kończy się wieloma wartościami NULL, trudnymi warunkami w zapytaniach i problemem z określeniem, który rekord jest naprawdę obowiązkowy.

Listy zapisane w jednym polu

Tekst w rodzaju 1,4,7,9 może wyglądać jak wygodna lista identyfikatorów, ale utrudnia wyszukiwanie, walidację i tworzenie relacji. W relacyjnej bazie relację wiele-do-wielu opisuje osobna tabela pośrednia.

Ślepe usuwanie duplikatów

Nie każda powtórzona wartość jest błędem. Cena na pozycji zamówienia może być celowo powielona, bo opisuje stan historyczny. Zanim przeniosę kolumnę do innej tabeli, pytam, czy oznacza ten sam fakt, czy podobną wartość w innym momencie.

Przeczytaj również: SQL Self Join - Jak łączyć tabelę z samą sobą?

Brak ograniczeń w bazie

Samo rozdzielenie tabel nie wystarczy. Bez kluczy obcych, ograniczeń unikalności i odpowiednich typów danych aplikacja może wstawić rekord wskazujący na nieistniejącego klienta albo dwa konta z tym samym adresem e-mail.

Najlepiej, gdy reguły są zapisane w dwóch miejscach. Kod aplikacji obsługuje komunikaty i proces biznesowy, a baza pilnuje podstawowej integralności nawet wtedy, gdy dane zapisuje skrypt, import lub drugie narzędzie.

Jak ocenić schemat przed wdrożeniem

Nie musisz analizować każdej tabeli za pomocą formalnych dowodów. Na początek sprawdź, czy każda tabela opisuje jeden główny obiekt, czy każda ma stabilny klucz oraz czy kolumny niekluczowe opisują właśnie ten obiekt.

• Czy jedna zmiana biznesowa wymaga aktualizacji wielu niezależnych rekordów?
• Czy można dodać obiekt bez tworzenia sztucznego zdarzenia?
• Czy usunięcie rekordu nie kasuje informacji należących do innego obiektu?
• Czy relacje wiele-do-wielu mają tabelę pośrednią?
• Czy historyczne wartości, takie jak cena transakcji, są przechowywane niezależnie od wartości bieżących?
• Czy indeksy wspierają klucze obce i najczęściej wykonywane zapytania?

Jeżeli odpowiedzi są pozytywne, projekt prawdopodobnie zmierza w dobrą stronę. W większości aplikacji internetowych rozsądnie zaprojektowana 3NF, poprawne ograniczenia i kilka dobrze dobranych indeksów dają lepszy rezultat niż skrajne rozbijanie danych albo przedwczesna denormalizacja.

Najważniejsza zasada jest prosta: tabela powinna przechowywać jeden fakt w jednym miejscu, chyba że istnieje konkretny, zmierzony powód, by zrobić inaczej. Taki model łatwiej rozwijać, testować i naprawiać, gdy aplikacja zacznie obsługiwać więcej użytkowników oraz bardziej złożone operacje.