Tworzenie tabel w MySQL

Aby rozpocząć tworzenie tabel, najpierw należy utworzyć bazę danych. Opis jak wykonać takie zadanie w MySQL jest w artykule Tworzenie bazy danych MySQL. Gdy mamy już utworzoną naszą bazę danych możemy przystąpić to tworzenia tabel. Samo tworzenie tabel nie powinno sprawić problemu pod warunkiem, że rozumiemy jak prawidłowo stworzyć tabele w naszym projekcie. W tej kwestii często potrzebne jest doświadczenie. Jednak jak zdobyć takie doświadczenie najlepiej zacznijmy od podstaw. W artykule Jak zrozumieć bazę danych tłumaczę jak przełożyć plik Excel na bazę danych na przykładzie MySQL. Dzięki temu można łatwiej zrozumieć różnicę między arkuszem a bazą danych.

Jeżeli już udało się nam stworzyć projekt na papierze naszej bazy danych, możemy przystąpić do budowy w naszej bazie. Oczywiście nie zniechęcaj się jeżeli za pierwszym razem się nie uda i sam dojdziesz do wniosku, że baza jest pokraczna. Każdy z nas tak właśnie zaczynał i każdy popełniał te same błędy, dopóki nie nabrał trochę doświadczenia.

W tym artykule stworzymy bardzo prostą bazę danych z dwiema tabelami z jedną relacją jeden do wielu. W tej chwili nie będę się skupiał na relacjach, ale jest to ta najtrudniejsza rzecz do zrobienia przy projektowaniu bazy danych.

Pierwsza tabela będzie się nazywała Word i będzie zawierać wszystkie słowa angielskie oraz tłumaczenia polskie do tego słowa, których się uczymy. Drugą tabelą będzie tabela Learn i będzie zawierać wszystkie wpisy, kiedy powtarzaliśmy dane słowo. Wiadomo, że słowo w całej bazie powinno występować tylko raz. Natomiast powtarzanie występuje wiele razy. To właśnie jest relacja jeden to wielu. Jednak taka relacja nie oznacza, że wpisów do tabeli Learn jest wiele. Może tak się zdarzyć, że nie będzie go wcale. Może być taka sytuacja, że możemy chcieć wpisać słowo, którego jeszcze nie powtarzaliśmy, ale chcemy to zrobić za parę dni. Jest to jak najbardziej poprawne.

CREATE TABLE `Word` (
  `id` bigint NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `createDate` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `word_en` varchar(100) CHARACTER SET utf8 NOT NULL,
  `word_pl` varchar(100) CHARACTER SET utf8 NOT NULL,
  `learned` bit(1) NOT NULL DEFAULT b'0'
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_polish_ci

Opisujemy teraz co się tu dzieje. Pierwsza kolumna to kolumna id, jest to automatyczna wartość wiersza nadawana automatycznie po dodaniu wpisu do bazy. Jest to tak zwany klucz główny dla danej tabeli. Powinien występować w każdej tabeli.

Drugą kolumną jest createDate, czyli data utworzenia wpisu. Teoretycznie taka data nie jest potrzebna i bardzo często nigdzie nie jest prezentowana, ale ja ją zawsze dodaje z tego powodu, aby mieć możliwość sprawdzenia kiedy dany wpis do bazy został dodany. Drugą kolumną, która można utworzyć jest updateDate. Jest to również data, ale ostatniej aktualizacji danych. Przydaje się to często gdy ktoś z pracowników upiera się, że dane zostały zmienione, a faktycznie nic takiego nie nastąpiło. Ja tutaj pomijam tę kolumnę.

Word_pl i word_en standardowo przechowują słowa o długości stu znaków. W pierwszym z nich przechowujemy słowo polskie a w drugim słowo w wersji angielskiej.

Ostania kolumna to learned. Kolumna ta przechowuje informację czy dane słowo jest już nauczone, czy nie. Możemy oczywiście zbudować kolejną tabelę, gdzie będziemy przechowywać odpowiednie statusy, ale w tym artykule chce zachować prostotę, aby był lepiej zrozumiały dla początkujących.

Końcowe wpisy konfigurują nam samą tabelę. Te wpisy są z reguły standardowe dla każdej tabeli.

Teraz zobaczmy drugą tabelę Learn.

CREATE TABLE `Learn` (
  `id` bigint NOT NULL AUTO_INCREMENT <meta http-equiv="content-type" content="text/html; charset=utf-8">PRIMARY KEY,
  `idWord` bigint NOT NULL,
  `createDate` datetime DEFAULT CURRENT_TIMESTAMP,
  KEY `Science_idWord_index` (`idWord`),
  CONSTRAINT `Science_Word_id_fk` FOREIGN KEY (`idWord`) REFERENCES `Word` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COLLATE=utf8_polish_ci

Tutaj podobnie jak we wcześniejszej tabeli mamy id, który już wcześniej opisałem więc nie będę tego robił ponownie

Jest również kolumna createDate, która w tym przypadku wskazuje kiedy został dodany dany wpis, ale jednocześnie kiedy słowo zostało powtórzone. Z taką konstrukcją może być problem, jeżeli chcemy dodać wpis z opóźnieniem. Wtedy daty nie będą się pokrywały. W takiej sytuacji trzeba by było stworzyć dodatkową kolumnę, gdzie będziemy przechowywać datę nauki a createDate zostawić tylko jako data wpisu. W naszym przypadku data wpisu i data nauki muszą być zawsze takie same.

Teraz zajmiemy się relacją. Na relacje składa się aż trzy wpisy

`idWord` bigint NOT NULL,
KEY `Science_idWord_index` (`idWord`),
CONSTRAINT `Science_Word_id_fk` FOREIGN KEY (`idWord`) REFERENCES `Word` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT

Pierwszy wpis to idWord, który tworzy kolumnę, w którym przechowujemy informacje o relacji. Jest to tak zwany klucz obcy. Czyli numer, który znajdziemy w tej kolumnie, będzie odpowiadał kolumnie id w tabeli Word. Dzięki temu możemy powiązać te dwie tabele.

Aby przyśpieszyć wyszukiwanie, ustawiamy KEY, czyli tworzymy indeks dla klucza obcego.

Ostatni element to poinformowanie bazy danych o relacji. My możemy tego nie robić, ale wtedy jesteśmy narażeni na niespójność danych. Przykładowo tworzymy sobie w tabeli Word jeden wpis. Czyli mamy wiersz z kluczem głównym id o numerze jeden, ale przez pomyłkę w tabeli Learn w idWord dajemy 2. Jeżeli nie dodamy tego wpisu do tabeli, to baza danych pozwoli nam to zapisać, ale ten wpis nie będzie powiązany z tabelą Word. Jeżeli wpis zostanie wprowadzony, to w takiej sytuacji baza danych zwróci błąd. Dzięki temu w tabeli nie będzie pustych wpisów niepowiązanych z tabelą Word. Dodatkowym argumentem za tym, aby stosować takie rozwiązanie, jest to, że taki błąd bardzo łatwo można znaleźć podczas tworzenia projektu i go poprawić, więc koszt jest dużo mniejszy.