Array e tabelle normalizzate a confronto
Scopra quando gli array sono la scelta giusta
Array e tabelle normalizzate a confronto è una lezione SQL Academy gratuita su CoddyKit. Questa è la lezione 4 di 4. Puoi leggere la lezione completa qui gratuitamente — poi esercitati direttamente nel browser con un editor di codice integrato e un tutor IA disponibile 24/7. Fa parte del percorso di apprendimento SQL Academy, e i tuoi progressi si sincronizzano tra il web e l'app CoddyKit. Il corso SQL Academy include 4 lezioni in totale.
Due modi per archiviare più valori
Quando una singola riga deve contenere più valori correlati, PostgreSQL offre due approcci principali: memorizzarli come colonna array nella stessa riga oppure creare una tabella figlia in cui ogni valore occupa una riga distinta.
Capire quando utilizzare ciascun approccio è una competenza fondamentale per progettare database efficienti e facilmente manutenibili.
L'approccio normalizzato
In uno schema completamente normalizzato, ogni dato risiede nella propria riga. Se un utente può avere più numeri di telefono, si crea una tabella user_phones con una chiave esterna che fa riferimento a users.
Questo è il modello relazionale classico e rappresenta la scelta predefinita nella maggior parte dei casi.
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');L'approccio basato sugli array
Il tipo TEXT[] di PostgreSQL (o qualsiasi altro tipo seguito da []) consente di memorizzare più valori direttamente all'interno di una singola colonna. Non è necessaria alcuna tabella aggiuntiva.
Gli stessi dati relativi ai numeri di telefono possono essere memorizzati in un'unica riga compatta per ogni utente.
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']);Interrogare gli array è facile
La ricerca all'interno di una colonna array è semplice con l'operatore ANY o con l'operatore @> (contiene). È possibile trovare tutti gli utenti che hanno uno specifico numero di telefono con una semplice clausola 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'];Quando convengono gli array: ricerche semplici
Gli array sono una scelta efficace quando:
- L'elenco di valori viene letto insieme come un'unità (tag, etichette, categorie)
- Non è mai necessario eseguire un join sui singoli elementi
- L'elenco ha un limite superiore naturale e viene aggiornato raramente per parti
Un esempio classico è memorizzare i tag di un articolo del blog. I tag vengono sempre recuperati tutti insieme e solo raramente si eseguono query sugli articoli in base a un singolo tag in un join complesso.
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);Quando convengono le tabelle normalizzate: relazioni
Le tabelle normalizzate sono la scelta migliore quando:
- I singoli valori necessitano di attributi propri (ad esempio, un numero di telefono ha un tipo: casa/lavoro)
- È necessario eseguire un join sui singoli valori
- I valori cambiano indipendentemente e frequentemente
- È necessaria l'integrità referenziale tramite chiavi esterne
-- 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');La differenza nell'indicizzazione
Con una tabella normalizzata, è possibile aggiungere un indice B-tree standard sulla chiave esterna o sulla colonna dei valori. Con gli array, è necessario un indice GIN (indice invertito generalizzato) per abilitare ricerche rapide all'interno dell'array.
Gli indici GIN funzionano bene, ma sono più grandi e più lenti da aggiornare rispetto agli indici 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'];Aggregazioni tra righe: meglio le tabelle normalizzate
Quando è necessario contare, raggruppare o aggregare singoli valori, le tabelle normalizzate sono molto più naturali. Per aggregare all'interno degli array è necessario usare unnest(), che prima espande l'array in righe, ricreando di fatto la struttura normalizzata al momento dell'esecuzione della query.
-- 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;Modifica degli elementi di un array
Aggiornare o eliminare un singolo elemento all'interno di un array richiede una sintassi poco agevole: è necessario sostituire l'intero array oppure usare array_remove(). In una tabella normalizzata, è sufficiente eseguire DELETE o UPDATE sulla riga specifica.
-- 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;Imposizione di valori validi
In una tabella normalizzata, è possibile usare una chiave esterna per garantire che ogni valore provenga da un insieme noto. Gli array non possono fare riferimento a un'altra tabella: non supportano le chiavi esterne.
Se è necessaria un'integrità referenziale garantita per ogni elemento, una tabella figlia è l'unica opzione.
-- 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;Una guida pratica alla scelta
Utilizzi un array quando i dati formano un semplice elenco piatto, vengono sempre letti come un'unità, non hanno attributi aggiuntivi per elemento e non richiedono integrità referenziale (ad esempio tag, etichette, parole chiave di ricerca).
Utilizzi una tabella figlia normalizzata quando ogni elemento ha attributi propri, esegue join o aggregazioni sui singoli valori, ha bisogno di chiavi esterne oppure aggiorna o elimina frequentemente i singoli elementi.
-- 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;Verifica rapida
Quale scenario è il più adatto per memorizzare i dati come array PostgreSQL invece che in una tabella figlia normalizzata?
Riepilogo della lezione
In questa lezione ha imparato quali sono i principali compromessi tra array e tabelle normalizzate in PostgreSQL.
- Gli array sono compatti e pratici per elenchi semplici letti come un'unità, come i tag, ma non supportano chiavi esterne, rendono poco agevoli gli aggiornamenti dei singoli elementi e richiedono indici GIN per ricerche rapide.
- Le tabelle normalizzate supportano attributi per singolo elemento, integrità tramite chiavi esterne, aggregazioni efficienti e semplici aggiornamenti a livello di riga, al costo di un join aggiuntivo.
- La scelta corretta dipende dal modo in cui esegue query, aggiorna e mette in relazione i dati, non solo dal modo in cui li memorizza.
Domande Frequenti
La lezione «Array e tabelle normalizzate a confronto» è gratuita?
Sì — il testo completo di «Array e tabelle normalizzate a confronto» è gratuito qui sul web. Per esercitarvi in modo interattivo (un editor di codice integrato e un tutor IA 24/7) e sbloccare il resto del corso SQL Academy, passa a CoddyKit PRO. Il corso SQL Academy include 4 lezioni in totale.
Cosa imparerò in «Array e tabelle normalizzate a confronto»?
Scopra quando gli array sono la scelta giusta Eserciti SQL Academy con codice pratico che esegui direttamente nel browser, e un tutor IA 24/7 risponde alle tue domande mentre lavori sulla lezione.
Ho bisogno di esperienza per iniziare SQL Academy?
Non è richiesta alcuna esperienza precedente. SQL Academy su CoddyKit è strutturato per principianti e studenti avanzati, quindi puoi iniziare da qui o dall'inizio e procedere al tuo ritmo. Questa è la lezione 4 di 4.
Quanto tempo richiede la lezione «Array e tabelle normalizzate a confronto»?
La maggior parte delle lezioni CoddyKit richiede circa 5–10 minuti. Ogni lezione è breve e interattiva, quindi fai progressi costanti e riprendi esattamente da dove hai lasciato su web e app.
Posso scrivere ed eseguire codice in questa lezione SQL Academy?
Sì. Ogni lezione SQL Academy include un editor di codice integrato, quindi scrivi ed esegui codice reale direttamente nel tuo browser e ricevi feedback istantaneo dall'IA — nessuna configurazione locale necessaria.
Tutte le lezioni di questo corso
- Nozioni di base sulle colonne array
- Cercare all'interno degli array
- UNNEST e aggregazione
- Array e tabelle normalizzate a confronto