Wstęp

W składni komendy SQL tworzącej tabelę próżno szukać klauzul opisujących jej funkcję w relacyjnej bazie danych. Na funkcję tabeli wpływa sposób w jaki jej wiersze powiązane są z innymi wierszami, stopień tych powiązań oraz częstotliwość modyfikacji danych. Powiązania tworzą między sobą różne wiersze pochodzące z tej samej lub dwóch tabel. W niniejszym materiale skupię się na powiązaniach wierszy dwóch tabel. Na tabelach zależnych tworzone są klucze obce odnoszące się do kluczy głównych tabel nadrzędnych.

Typ powiązania

Można rozróżnić trzy typy powiązań:

• (1:1) jeden do jeden. Czyli w zero lub jednym wierszu tabeli zależnej wartość klucza obcego może równać się jednej wartości klucza głównego tabeli nadrzędnej.
• (1:N) jeden do wiele. Czyli w zero lub wielu wierszach tabeli zależnej wartość klucza obcego może równać się jednej wartości klucza głównego tabeli nadrzędnej.
• (M:N) wiele do wiele. Czyli w zero lub wielu wierszach pierwszej tabeli wartość klucza obcego może równać się jednej wartości klucza głównego drugiej tabeli i odwrotnie. W zero lub wielu wierszach drugiej tabeli wartość klucza obcego może równać się jednej wartości klucza głównego pierwszej tabeli.

Relacyjne motory nie radzą sobie z powiązaniami wiele do wiele. Takie powiązanie trzeba rozbić na dwa powiązania jeden do wiele przy pomocy tabeli pośredniej. Omawiam ją w dalszej części materiału.

Stopień powiązania

Stopień powiązania określa minimalną i maksymalną liczbę wierszy w tabeli zależnej posiadającą wartość klucza obcego równą wartości klucza głównego tabeli nadrzędnej. Zakres można zdefiniować precyzyjnie. Na przykład tabela zależna może mieć nie mniej niż 1 i co najwyżej 5 wierszy z identyczną wartością klucza głównego tabeli nadrzędnej w kluczu obcym. Innymi słowy jednemu wierszowi tabeli nadrzędnej odpowiada nie mniej niż 1 i co najwyżej 5 wierszy tabeli zależnej.

W takim przypadku klucz obcy do wymuszenia spójności nie wystarcza. Modyfikowanie danych aby zapewnić przestrzeganie reguły biznesowej powinno zostać oprogramowane. Jest to czwarty poziom spójności danych zwany spójnością na poziomie użytkownika lub spójnością na poziomie aplikacji. Według mnie kod wymuszający precyzyjne powiązanie powinien być jak najbliżej danych czyli umieszczony w bazie danych. Na przykład w postaci procedur składowanych czy wyzwalaczy. Dzięki takiemu rozwiązaniu reguły przestrzegane są niezależnie od tego czy dane pochodzą z Twojej aplikacji, są wstawiane ręcznie czy importowane z innego systemu. W przypadku wykorzystania kodu składowanego w bazie danych oszczędzasz na komunikacji sieciowej co może znacznie przyspieszyć działanie aplikacji.

Tabela główna

Pierwszym funkcjonalnym typem tabeli jest tabela główna. To fundament modelu danych. W relacyjnej bazie danych pełni kluczową rolę jako podstawowy magazyn danych o głównych bytach istotnych dla organizacji. Na przykład o osobie, przedmiocie, miejscu lub zdarzeniu.

Dlatego identyfikacji tabel głównych dokonuje się na wczesnym etapie projektowania bazy danych. Dane w takiej tabeli są unikalne w skali całej bazy danych i podlegają częstym zmianom. Są gromadzone, aktualizowane i wykorzystywane w codziennej działalności organizacji. Służą jako źródło do przetwarzania w informacje potrzebne do zarządzania organizacją, do obsługi procesów oraz analiz biznesowych. Nazwa tabeli jednoznacznie określa byt o którym przechowuje dane. Na przykład książka, autor, student czy wykład.

Tabela pośrednia

Drugim typem jest tabela pośrednia. Używana do prawidłowego odwzorowania relacji wiele do wiele. Zawiera kopie kluczy głównych z obu tabel uczestniczących w relacji. Te kolumny pełnią dwie funkcje: tworzą złożony klucz główny tabeli pośredniej i każda z nich jest jednocześnie kluczem obcym odnoszącym się do jednej z tabel głównych. Klucz złożony to klucz składający się z więcej niż jednej kolumny.

Opcjonalnie tabela może zawierać dodatkowe kolumny. Tabela pośrednia ogranicza redundancję danych. Dzięki niej nie ma potrzeby duplikowania danych z tabel głównych. Nazwa tabeli powinna jasno oddawać naturę relacji. Zazwyczaj składa się z nazw dwóch powiązanych tabel głównych. Na przykład książka_autor czy student_wykład.

Tabela dodatkowa

Trzeci typ to tabela dodatkowa. Używana do modelowania podrzędnych aspektów jednego głównego bytu. Albo aspektów które nie dotyczą wszystkich jego instancji. Z tabelą główną zazwyczaj pozostaje w relacji jeden do jeden. Tabela dodatkowa może być wykorzystana do wspierania bezpieczeństwa. Czyli dane, które chcesz chronić wynosisz z szeroko dostępnej tabeli głównej do tabeli dodatkowej o bardzo ograniczonym i audytowanym dostępie. Na przykład hasło studenta, honorarium autora czy inne dane wrażliwe. Tabela dodatkowa zawiera tylko dane odnoszące się do podzbioru. Z tabeli głównej powiela jedynie klucz główny, który w tabeli dodatkowej staje się kluczem obcym. Tworząc odpowiednie więzy integralności na kolumnach klucza obcego możesz wymusić poziom powiązania zero lub jeden.

Tabela dodatkowa jest podstawą modelowania dziedziczenia. Na przykład tabela główna inwentarz zawiera podstawowe informacje o przedmiocie jak nazwa czy numer inwentarzowy. Powiązana z nią tabela dodatkowa inwentarz_sprzęt zawiera dane o numerze seryjnym i producencie, a tabela dodatkowa inwentarz_książki o numerze ISBN i autorze.

Dzięki takiemu podziałowi unikasz powielania pustej kolumny autor dla sprzętu czy pustej kolumny numer seryjny dla książki. Tabela dodatkowa pozwala zachować specjalizację tabel i zasadę jedna tabela to jeden byt. Co poprawia czytelność struktury bazy danych. Podobnie jak nazwa w pierwszym członie nawiązująca do tabeli głównej. Na przykład: autor_honorarium czy student_hasło.

Tabela słownikowa

Tabela słownikowa używana jest do zapewniania integralności danych poprzez ograniczenie zakresu dozwolonych wartości dla określonych kolumn tabeli głównej. Przechowuje ustalony zestaw dopuszczalnych wartości, które mogą zostać wpisane w kolumnę innej tabeli. Wymusza przestrzeganie zasad biznesowych i chroni bazę danych przed błędami oraz niespójnymi wpisami. Na przykład zawiera listę województw, listę języków narodowych czy listę kategorii książki.

Korzyści:

• wymusza spójność danych – tylko wartości z tabeli słownikowej mogą być wpisane do kolumn w tabeli głównej
• ułatwia aktualizację – zmiana nazwy lub wartości w tabeli słownikowej wpływa na wszystkie powiązane dane
• redukuje błędy użytkownika – często używana jako źródło danych do rozwijanych list w formularzach
• umożliwia stosowanie reguł biznesowych – na przykład tylko dostawcy z wybranych województw mogą zostać dodani do systemu

Dane tabeli słownikowej zmieniają się rzadko – po początkowym wypełnieniu, operacje modyfikujące są minimalne​. Tabela słownikowa może nawet składać się z jedynie dwóch kolumn. Jedna to klucz główny służący do wyszukiwania odpowiedniej wartości w kolumnie drugiej. Nazwa tabeli słownikowej powinna wskazywać na miejsce użycia i zawartość. Na przykład autor_język czy książka_kategoria.

Podsumowanie

Dziękuję za obejrzenie niniejszego materiału. Wiesz z niego, że tabele utworzone w relacyjnej bazie danych można, ze względu na pełnioną funkcję, podzielić na cztery typy:

• tabela główna magazynuje często modyfikowane dane o bytach i zdarzeniach interesujących dla organizacji
• tabela pośrednia używana jest do rozwikłania powiązania wiele do wiele
• tabela dodatkowa zawiera dane uzupełniające do tabeli głównej, które nie są kluczowe dla podstawowych operacji na bycie
• tabela słownikowa zawierająca stosunkowo niewiele, z góry zdefiniowanych, statycznych wartości służących do wypełnienia kolumn w tabeli głównej

Wiesz także, że dla wymuszenia precyzyjnego stopnia powiązania nie wystarczy klucz obcy. Trzeba wspomóc się oprogramowaniem procesu zmiany wartości.

Niech dostatek referencyjnej integralności zawsze Ci towarzyszy! A jeśli chcesz aby wiersze Twojej tabeli zależnej nigdy nie były osierocone koniecznie śledź mój blog.