Tablice a znormalizowane tabele
Dowiedz się, kiedy tablice są właściwym wyborem
Tablice a znormalizowane tabele to bezpłatna lekcja SQL Academy na CoddyKit. To lekcja 4 z 4. Możesz przeczytać całą lekcję poniżej za darmo — a potem ćwiczyć ją interaktywnie w przeglądarce z wbudowanym edytorem kodu i tutorem AI dostępnym 24/7. To część ścieżki edukacyjnej SQL Academy, a Twój postęp synchronizuje się między webem a aplikacją CoddyKit. Kurs SQL Academy zawiera 4 lekcji w sumie.
Dwa sposoby przechowywania wielu wartości
Gdy pojedynczy wiersz musi przechowywać wiele powiązanych wartości, PostgreSQL oferuje dwa główne podejścia: przechowywanie ich w kolumnie tablicowej w tym samym wierszu albo utworzenie osobnej tabeli podrzędnej, w której każda wartość zajmuje własny wiersz.
Zrozumienie, kiedy stosować każde z tych podejść, jest kluczową umiejętnością przy projektowaniu wydajnych i łatwych w utrzymaniu baz danych.
Podejście znormalizowane
W całkowicie znormalizowanym schemacie każda część danych znajduje się we własnym wierszu. Jeśli użytkownik może mieć wiele numerów telefonu, należy utworzyć tabelę user_phones z kluczem obcym odwołującym się do tabeli users.
Jest to klasyczny model relacyjny i domyślny wybór w większości sytuacji.
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE user_phones (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
phone TEXT NOT NULL
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO user_phones (user_id, phone) VALUES
(1, '+1-555-0101'),
(1, '+1-555-0102'),
(2, '+1-555-0200');Podejście z użyciem tablicy
Typ PostgreSQL TEXT[] (lub dowolny inny typ zakończony przez []) pozwala przechowywać wiele wartości bezpośrednio w jednej kolumnie. Nie jest potrzebna dodatkowa tabela.
Te same dane dotyczące numerów telefonów można przechowywać w jednym zwartym wierszu na użytkownika.
CREATE TABLE users_with_phones (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
phones TEXT[]
);
INSERT INTO users_with_phones (name, phones) VALUES
('Alice', ARRAY['+1-555-0101', '+1-555-0102']),
('Bob', ARRAY['+1-555-0200']);Zapytania dotyczące tablic są proste
Wyszukiwanie w kolumnie tablicowej jest proste dzięki operatorowi ANY lub operatorowi @> (zawiera). Wszystkich użytkowników mających określony numer telefonu można znaleźć za pomocą prostej klauzuli WHERE.
-- Find users who have a specific phone number
SELECT name
FROM users_with_phones
WHERE '+1-555-0101' = ANY(phones);
-- Or using the array-contains operator
SELECT name
FROM users_with_phones
WHERE phones @> ARRAY['+1-555-0101'];Kiedy tablice są lepszym wyborem: proste wyszukiwanie
Tablice są dobrym wyborem, gdy:
- lista wartości jest odczytywana jako całość (tagi, etykiety, kategorie)
- nie ma potrzeby wykonywania JOIN na poszczególnych elementach
- lista ma naturalne ograniczenie liczby elementów i rzadko jest aktualizowana częściowo
Klasycznym przykładem jest przechowywanie tagów przy wpisie na blogu. Wszystkie tagi są zawsze pobierane jednocześnie, a wyszukiwanie wpisów po pojedynczym tagu w złożonym złączeniu jest rzadko potrzebne.
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT[]
);
INSERT INTO posts (title, tags) VALUES
('Intro to SQL', ARRAY['sql', 'beginner', 'database']),
('Advanced Indexes', ARRAY['sql', 'performance', 'indexes']),
('NoSQL Overview', ARRAY['nosql', 'beginner']);
-- Get all posts tagged 'beginner'
SELECT title FROM posts
WHERE 'beginner' = ANY(tags);Kiedy lepiej użyć znormalizowanych tabel: relacje
Znormalizowane tabele są lepszym wyborem, gdy:
- poszczególne wartości wymagają własnych atrybutów (np. numer telefonu ma typ: domowy/służbowy)
- potrzebne jest wykonywanie JOIN na poszczególnych wartościach
- wartości zmieniają się niezależnie i często
- potrzebna jest integralność referencyjna za pomocą kluczy obcych
-- Phone numbers need a 'type' attribute — array can't do this cleanly
CREATE TABLE user_phones (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
phone TEXT NOT NULL,
type TEXT CHECK (type IN ('home', 'work', 'mobile'))
);
INSERT INTO user_phones (user_id, phone, type) VALUES
(1, '+1-555-0101', 'home'),
(1, '+1-555-0102', 'work');Różnica w indeksowaniu
W przypadku znormalizowanej tabeli można dodać standardowy indeks B-tree na kolumnie klucza obcego lub wartości. W przypadku tablic potrzebny jest indeks GIN (uogólniony indeks odwrócony), aby umożliwić szybkie wyszukiwanie wewnątrz tablicy.
Indeksy GIN działają dobrze, ale są większe i wolniej się aktualizują niż indeksy B-tree.
-- Index for fast array element lookups
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- Now this query uses the index efficiently
EXPLAIN SELECT title FROM posts
WHERE tags @> ARRAY['sql'];Agregowanie między wierszami: przewaga znormalizowanych tabel
Gdy trzeba zliczać, grupować lub agregować poszczególne wartości, znormalizowane tabele są znacznie bardziej naturalnym rozwiązaniem. Agregowanie wartości wewnątrz tablic wymaga użycia unnest(), które najpierw rozwija tablicę do postaci wierszy — w praktyce odtwarzając znormalizowaną strukturę w czasie wykonywania zapytania.
-- Count posts per tag (array approach — needs unnest)
SELECT tag, COUNT(*) AS post_count
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY post_count DESC;
-- With a normalized post_tags table this would be simpler:
-- SELECT tag, COUNT(*) FROM post_tags GROUP BY tag;Modyfikowanie elementów tablicy
Aktualizowanie lub usuwanie pojedynczego elementu tablicy wymaga niezręcznej składni — trzeba zastąpić całą tablicę albo użyć array_remove(). W znormalizowanej tabeli wystarczy wykonać DELETE lub UPDATE na określonym wierszu.
-- Remove a single tag from an array column
UPDATE posts
SET tags = array_remove(tags, 'beginner')
WHERE id = 1;
-- Append a new tag
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;
SELECT title, tags FROM posts WHERE id = 1;Wymuszanie poprawnych wartości
W znormalizowanej tabeli można użyć klucza obcego, aby wymusić, by każda wartość pochodziła ze znanego zbioru. Tablice nie mogą odwoływać się do innej tabeli — nie obsługują kluczy obcych.
Jeśli dla każdego elementu potrzebna jest gwarantowana integralność referencyjna, jedyną opcją jest tabela podrzędna.
-- Normalized: only valid category IDs allowed (FK enforced)
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);
CREATE TABLE post_categories (
post_id INT REFERENCES posts(id),
category_id INT REFERENCES categories(id),
PRIMARY KEY (post_id, category_id)
);
-- Array: no constraint possible — any text value is accepted
-- UPDATE posts SET tags = ARRAY['totally_invalid_tag'] WHERE id = 1;Praktyczny przewodnik po wyborze
Tablicę należy wybrać, gdy dane stanowią prostą płaską listę, są zawsze odczytywane jako całość, elementy nie mają dodatkowych atrybutów, a integralność referencyjna nie jest wymagana (np. tagi, etykiety, słowa kluczowe wyszukiwania).
Znormalizowaną tabelę podrzędną należy wybrać, gdy każdy element ma własne atrybuty, wykonywane są złączenia lub agregacje na poszczególnych wartościach, potrzebne są klucze obce albo poszczególne elementy są często aktualizowane lub usuwane.
-- Summary example: tags as array (good fit)
SELECT title, tags
FROM posts
WHERE tags @> ARRAY['sql']
ORDER BY title;
-- Unnest when you need row-level processing
SELECT title, unnest(tags) AS tag
FROM posts
ORDER BY title, tag;Szybkie sprawdzenie
W którym scenariuszu najlepiej sprawdzi się przechowywanie danych jako tablicy PostgreSQL zamiast w znormalizowanej tabeli podrzędnej?
Podsumowanie lekcji
W tej lekcji poznano najważniejsze kompromisy związane ze stosowaniem tablic i znormalizowanych tabel w PostgreSQL.
- Tablice są zwarte i wygodne w przypadku płaskich list odczytywanych jako całość, takich jak tagi — ale nie obsługują kluczy obcych, utrudniają aktualizowanie pojedynczych elementów i wymagają indeksów GIN do szybkiego wyszukiwania.
- Znormalizowane tabele obsługują atrybuty poszczególnych elementów, integralność kluczy obcych, wydajne agregowanie i proste aktualizacje na poziomie wierszy — kosztem dodatkowego złączenia.
- Właściwy wybór zależy od sposobu wyszukiwania, aktualizowania i wiązania danych, a nie tylko od sposobu ich przechowywania.
Ucz się SQL dzięki korepetycjom AI — za darmo
Pisz i uruchamiaj kod w przeglądarce, otrzymuj natychmiastową pomoc od korepetytora AI dostępnego 24/7 i kontynuuj naukę w sieci lub w aplikacji.
- Kursy
- 46
- Lekcje
- 183
Często zadawane pytania
Czy lekcja „Tablice a znormalizowane tabele” jest bezpłatna?
Tak — pełny tekst „Tablice a znormalizowane tabele” jest dostępny za darmo tutaj w sieci. Aby ćwiczyć ją interaktywnie (wbudowany edytor kodu i tutor AI dostępny 24/7) i odblokować resztę kursu SQL Academy, przejdź na CoddyKit PRO. Kurs SQL Academy zawiera 4 lekcji w sumie.
Co nauczysz się w „Tablice a znormalizowane tabele”?
Dowiedz się, kiedy tablice są właściwym wyborem Ćwiczysz SQL Academy z praktycznym kodem, który uruchamiasz bezpośrednio w przeglądarce, a tutor AI dostępny 24/7 odpowiada na Twoje pytania podczas pracy nad lekcją.
Czy potrzebuję doświadczenia, aby zacząć SQL Academy?
Nie wymagamy żadnego doświadczenia. SQL Academy w CoddyKit jest strukturyzowany dla początkujących i zaawansowanych użytkowników, więc możesz zacząć tutaj lub od początku i uczyć się w swoim tempie. To lekcja 4 z 4.
Ile czasu zajmuje lekcja „Tablice a znormalizowane tabele”?
Większość lekcji CoddyKit trwa około 5–10 minut. Każda lekcja to mały, interaktywny krok, dzięki czemu robisz systematyczne postępy i zawsze wracasz dokładnie do tego samego miejsca — na webie i w aplikacji.
Czy mogę pisać i uruchamiać kod w tej lekcji SQL Academy?
Tak. Każda lekcja SQL Academy zawiera wbudowany edytor kodu, więc piszesz i uruchamiasz prawdziwy kod bezpośrednio w przeglądarce i od razu otrzymujesz sprzężenie zwrotne od AI — bez konfiguracji na komputerze.
Wszystkie lekcje w tym kursie
- Podstawy kolumn tablicowych
- Wyszukiwanie wewnątrz tablic
- UNNEST i agregowanie
- Tablice a znormalizowane tabele