Progetto pratico: costruire un piccolo database per un negozio

Bentornato.

Nella lezione precedente hai imparato gli indici e le prestazioni di base.

Hai imparato:

• perché alcune query possono diventare lente;
• cosa sono gli indici;
• come EXPLAIN ANALYZE aiuta a capire le query;
• perché gli indici sono utili;
• perché gli indici non sono gratis;
• quando gli indici possono aiutare;
• quando gli indici sono solo decorazioni costose.

Molto bene.

Ora è il momento di mettere insieme molti pezzi.

Oggi costruiremo un piccolo progetto pratico con PostgreSQL.

Non un esempio finto con una sola tabella.

Non un database misterioso con nomi tipo table1 e data2.

No.

Oggi creeremo un piccolo database per un negozio.

Avrà:

customers
categories
products
orders
order_items

Questo è vicino alla progettazione di un database reale.

Abbastanza piccolo da capire.

Abbastanza reale da essere utile.

Come una spada da allenamento.

Ma per SQL.

Meno pericolosa.

Di solito.

Cosa imparerai

In questa lezione imparerai a:

• progettare un piccolo database relazionale;
• creare più tabelle collegate;
• usare chiavi primarie;
• usare chiavi esterne;
• inserire dati realistici;
• leggere dati con JOIN;
• calcolare il totale degli ordini;
• contare gli ordini per cliente;
• calcolare la spesa totale per cliente;
• calcolare le vendite per categoria;
• usare funzioni aggregate in un progetto pratico;
• creare indici utili;
• pensare come un progettista di database.

Alla fine di questa lezione avrai un mini progetto database funzionante.

Non solo comandi SQL casuali.

Una struttura reale.

Una piccola macchina da database.

Con clienti.

Prodotti.

Ordini.

E abbastanza relazioni da far sentire importante PostgreSQL.

Idea del progetto

Stiamo costruendo un piccolo database per un negozio.

Il negozio vende prodotti.

Ogni prodotto appartiene a una categoria.

I clienti possono creare ordini.

Ogni ordine può contenere molti prodotti.

Questo significa che abbiamo bisogno di più tabelle.

La struttura sarà:

categories
products
customers
orders
order_items

Relazioni:

categories -> products
customers  -> orders
orders     -> order_items
products   -> order_items

In parole semplici:

Una categoria ha molti prodotti.
Un cliente ha molti ordini.
Un ordine ha molti dettagli ordine.
Un prodotto può apparire in molti dettagli ordine.

Qui i database relazionali diventano davvero utili.

Invece di mettere tutto in una tabella gigante e caotica, separiamo i dati in tabelle pulite.

Perché una tabella gigante sembra facile all’inizio.

E terribile dopo.

Come mettere attrezzi, calzini, documenti, password e pasta nello stesso cassetto.

Possibile.

Ma non saggio.

Preparare il database

Apri PostgreSQL:

sudo -iu postgres psql

Collegati al tuo database:

\c learning_postgresql

Se il database non esiste, crealo:

CREATE DATABASE learning_postgresql;

Poi collegati:

\c learning_postgresql

Ora elimina le vecchie tabelle, se esistono.

L’ordine è importante perché alcune tabelle dipendono da altre.

DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS categories;

Eliminiamo prima order_items perché dipende da orders e products.

PostgreSQL protegge le relazioni.

Come un bibliotecario severo.

Ma con più punti e virgola.

Creare la tabella Categories

Una categoria raggruppa prodotti.

Esempi:

Electronics
Furniture
Office
Kitchen

Crea la tabella:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Dettagli importanti:

• id è la chiave primaria;
• name è obbligatorio;
• name deve essere unico.

Non vogliamo due categorie chiamate Electronics.

Una Electronics basta.

Anche per PostgreSQL.

Creare la tabella Products

Ogni prodotto appartiene a una categoria.

Crea la tabella:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(150) NOT NULL,
  price NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  stock_quantity INTEGER NOT NULL CHECK (stock_quantity >= 0),
  category_id INTEGER NOT NULL REFERENCES categories(id)
);

Questa tabella ha:

• nome del prodotto;
• prezzo del prodotto;
• quantità in magazzino;
• relazione con la categoria.

La relazione importante è:

category_id INTEGER NOT NULL REFERENCES categories(id)

Questo significa:

Ogni prodotto deve appartenere a una categoria esistente.

PostgreSQL non permetterà a un prodotto di puntare a una categoria che non esiste.

Bene.

Perché le categorie immaginarie non sono ottime per i report.

O per la salute mentale.

Creare la tabella Customers

I clienti fanno ordini.

Crea la tabella:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(100) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Dettagli importanti:

• email è unica;
• name è obbligatorio;
• created_at viene compilato automaticamente.

Nelle applicazioni reali, l’email è spesso usata per login o comunicazione.

Quindi email duplicate sono di solito una pessima idea.

Due Anna possono esistere.

Due email identiche no.

PostgreSQL è d’accordo.

Creare la tabella Orders

Un ordine appartiene a un cliente.

Crea la tabella:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  status VARCHAR(50) NOT NULL DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Questa tabella memorizza l’ordine.

Ma nota una cosa.

Non c’è una colonna total.

Perché?

Perché il totale può essere calcolato da order_items.

In molti sistemi reali i totali vengono anche salvati, per prestazioni e storico.

Ma per imparare, calcolare i totali è meglio.

Ti insegna come funzionano i dati.

E PostgreSQL ama insegnare.

In modo severo.

Creare la tabella Order Items

Un ordine può contenere molti prodotti.

Per esempio:

Ordine 1:
- Laptop x 1
- Mouse x 2

Per questo ci serve order_items.

Crea la tabella:

CREATE TABLE order_items (
  id SERIAL PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0)
);

Dettagli importanti:

• order_id si collega a orders;
• product_id si collega a products;
• quantity deve essere maggiore di zero;
• unit_price salva il prezzo del prodotto al momento dell’ordine.

Perché salvare unit_price qui?

Perché i prezzi dei prodotti possono cambiare dopo.

Immagina che un prodotto costi 25 oggi.

Domani costa 30.

Gli ordini vecchi devono ricordare il vecchio prezzo.

Altrimenti i tuoi report diventano una scena del crimine storica.

Quindi order_items.unit_price salva il prezzo usato in quell’ordine.

Molto pratico.

Molto reale.

Inserire le categorie

Aggiungi le categorie:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office'),
  ('Kitchen');

Controllale:

SELECT * FROM categories;

Dovresti vedere quattro categorie.

Piccolo inizio.

La civiltà del database comincia qui.

Inserire i prodotti

Aggiungi i prodotti:

INSERT INTO products (name, price, stock_quantity, category_id)
VALUES
  ('Laptop', 900.00, 5, 1),
  ('Mouse', 25.00, 30, 1),
  ('Keyboard', 70.00, 15, 1),
  ('Desk Chair', 150.00, 8, 2),
  ('Bookshelf', 120.00, 4, 2),
  ('Notebook', 5.00, 100, 3),
  ('Pen', 2.00, 200, 3),
  ('Whiteboard', 80.00, 3, 3),
  ('Coffee Mug', 12.00, 40, 4),
  ('Kitchen Knife', 35.00, 20, 4);

Controlla i prodotti:

SELECT * FROM products;

Ora abbiamo prodotti collegati alle categorie.

Ma vediamo ancora category_id.

Gli esseri umani preferiscono i nomi.

Quindi tra poco useremo JOIN.

Perché gli ID misteriosi non sono una interfaccia utente.

Sono una richiesta d’aiuto.

Inserire i clienti

Aggiungi i clienti:

INSERT INTO customers (email, name)
VALUES
  ('anna@example.com', 'Anna'),
  ('marco@example.com', 'Marco'),
  ('sofia@example.com', 'Sofia'),
  ('luca@example.com', 'Luca');

Controlla i clienti:

SELECT * FROM customers;

Ora abbiamo persone che possono comprare cose.

Molto pericoloso.

Per i portafogli.

Inserire gli ordini

Crea alcuni ordini:

INSERT INTO orders (customer_id, status)
VALUES
  (1, 'paid'),
  (1, 'paid'),
  (2, 'paid'),
  (3, 'new'),
  (4, 'cancelled');

Questo significa:

Anna ha due ordini.
Marco ha un ordine.
Sofia ha un ordine nuovo.
Luca ha un ordine annullato.

Controlla gli ordini:

SELECT * FROM orders;

Gli ordini esistono.

Ma sono vuoti.

Un ordine senza prodotti è come una scatola della pizza senza pizza.

Tecnicamente è una scatola.

Emotivamente deludente.

Inserire i dettagli ordine

Ora aggiungi i prodotti agli ordini:

INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES
  (1, 1, 1, 900.00),
  (1, 2, 2, 25.00),
  (1, 3, 1, 70.00),

  (2, 6, 10, 5.00),
  (2, 7, 20, 2.00),

  (3, 4, 1, 150.00),
  (3, 5, 1, 120.00),

  (4, 9, 2, 12.00),
  (4, 10, 1, 35.00),

  (5, 2, 1, 25.00);

Controlla i dettagli ordine:

SELECT * FROM order_items;

Ora abbiamo una vera mini struttura da negozio.

Clienti.

Ordini.

Prodotti.

Dettagli.

Prezzi.

Quantità.

Relazioni.

Bellissima piccola macchina da database.

Mostrare prodotti con nomi delle categorie

Cominciamo con un semplice JOIN.

SELECT
  p.id,
  p.name AS product_name,
  p.price,
  p.stock_quantity,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY p.id;

Questo restituisce dati prodotto leggibili.

Invece di:

Laptop | category_id = 1

otteniamo:

Laptop | Electronics

Questo è ciò che serve nelle applicazioni reali.

Gli utenti non devono decifrare ID.

Gli utenti soffrono già abbastanza.

Soprattutto quando le password richiedono un simbolo, un numero, una runa antica e stabilità emotiva.

Mostrare ordini con nomi dei clienti

Ora mostra gli ordini con i nomi dei clienti:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  c.email,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Questo ci dice chi ha fatto ogni ordine.

Molto utile.

Una tabella con solo customer_id è tecnicamente corretta.

Ma dati leggibili sono meglio.

Dati leggibili separano un buon pannello admin da una caverna con un foglio Excel.

Mostrare i dettagli completi degli ordini

Ora mostriamo i dettagli ordine con informazioni su prodotto e cliente.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM order_items AS oi
JOIN orders AS o
ON oi.order_id = o.id
JOIN customers AS c
ON o.customer_id = c.id
JOIN products AS p
ON oi.product_id = p.id
ORDER BY o.id, oi.id;

Questa è una query seria.

Collega:

order_items
orders
customers
products

Ora possiamo vedere:

Quale cliente ha comprato quale prodotto.
Quanti pezzi.
A quale prezzo.
Con quale totale.

Questa è la potenza dei database relazionali.

Tabelle piccole.

Collegate bene.

Risposte utili.

Niente cassetto del caos.

Calcolare il totale di ogni ordine

Ora calcoliamo il valore totale di ogni ordine.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name, o.status
ORDER BY o.id;

Qui usiamo:

• JOIN;
• SUM;
• GROUP BY.

Una combinazione molto utile.

Il database calcola i totali dai dettagli ordine.

Nessuna calcolatrice necessaria.

Nessun file Excel nervoso necessario.

PostgreSQL gestisce tutto.

Con dignità.

Calcolare solo gli ordini pagati

Di solito gli ordini annullati non devono contare come ricavi.

Quindi possiamo filtrare per stato.

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Questo mostra solo gli ordini pagati.

Molto importante.

Perché contare gli ordini annullati come ricavi è un ottimo modo per creare report bellissimi e decisioni aziendali terribili.

I numeri belli possono mentire.

SQL aiuta.

Ma solo se chiedi correttamente.

Ricavi totali

Ora calcoliamo i ricavi totali dagli ordini pagati.

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Questo restituisce un solo numero.

Un numero molto da business.

Il tipo di numero che le persone mettono su una dashboard.

Poi lo fissano seriamente.

Anche se la dashboard ha solo tre righe.

Ordini per cliente

Conta quanti ordini ha ogni cliente.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
GROUP BY c.name
ORDER BY orders_count DESC;

Usiamo LEFT JOIN perché potremmo voler mostrare anche clienti con zero ordini.

Nei nostri dati attuali ogni cliente ha almeno un ordine.

Ma nella vita reale molti clienti si registrano e non comprano nulla.

Molto normale.

Molto doloroso per il marketing.

Ordini pagati per cliente

Ora conta solo gli ordini pagati.

SELECT
  c.name AS customer_name,
  COUNT(o.id) AS paid_orders_count
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
GROUP BY c.name
ORDER BY paid_orders_count DESC;

Dettaglio importante:

La condizione sullo stato è dentro ON:

AND o.status = 'paid'

Perché?

Perché vogliamo comunque vedere tutti i clienti.

Se mettessimo questa condizione in WHERE, i clienti senza ordini pagati potrebbero sparire.

Questo è un classico dettaglio di LEFT JOIN.

Piccolo dettaglio.

Grande differenza.

Trappola database numero 247.

Collezionale tutte.

Spesa totale per cliente

Ora calcoliamo quanto ha speso ogni cliente negli ordini pagati.

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Qui usiamo COALESCE.

Perché?

Perché se un cliente non ha ordini pagati, la somma è NULL.

Noi vogliamo 0.

Questo:

COALESCE(SUM(oi.quantity * oi.unit_price), 0)

significa:

Se la somma esiste, mostrala.
Se la somma è NULL, mostra 0.

Molto pratico.

Molto adatto alle dashboard.

Molto “non spaventare il contabile”.

Vendite per categoria

Ora calcoliamo le vendite per categoria.

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Questo risponde alla domanda:

Quali categorie generano più ricavi?

Molto utile per il business.

Forse vince Electronics.

Forse vince Office.

Forse Kitchen sorprende tutti con le tazze.

Mai sottovalutare le tazze.

Numero di prodotti venduti

Ora contiamo quante unità di ogni prodotto sono state vendute.

SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold
FROM products AS p
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY p.name
ORDER BY units_sold DESC;

Questo è utile per pianificare il magazzino.

Se le penne si vendono molto, ordina più penne.

Se le librerie non si vendono, forse smetti di costruire un impero di legno.

I dati aiutano le decisioni.

A volte in modo brutale.

Prodotti mai venduti

Ora troviamo i prodotti che non sono mai stati venduti in ordini pagati.

SELECT
  p.name AS product_name
FROM products AS p
LEFT JOIN order_items AS oi
ON p.id = oi.product_id
LEFT JOIN orders AS o
ON oi.order_id = o.id
AND o.status = 'paid'
GROUP BY p.id, p.name
HAVING COUNT(o.id) = 0
ORDER BY p.name;

Questa query è un po’ più avanzata.

Usa:

• LEFT JOIN;
• GROUP BY;
• HAVING;
• COUNT.

L’idea è:

Mostra i prodotti con zero ordini pagati.

Molto utile.

Molto business.

Molto “perché ne abbiamo comprati 500?”.

Prodotti con poco stock

Mostra prodotti con poco stock.

SELECT
  name AS product_name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Questa è una query semplice.

Ma molto pratica.

In un sistema reale, potrebbe alimentare un avviso:

Stock basso. Ordina di più.

I database non servono solo a conservare dati.

Possono aiutarti a notare problemi.

Come un assistente silenzioso.

Che parla solo SQL.

Creare indici utili

Ora aggiungiamo indici sensati per questo progetto.

Le chiavi primarie hanno già indici automaticamente.

Ma le chiavi esterne sono buone candidate per gli indici.

Crea gli indici:

CREATE INDEX idx_products_category_id
ON products(category_id);

CREATE INDEX idx_orders_customer_id
ON orders(customer_id);

CREATE INDEX idx_order_items_order_id
ON order_items(order_id);

CREATE INDEX idx_order_items_product_id
ON order_items(product_id);

Questi indici possono aiutare con i JOIN.

Ora crea indici per filtri comuni:

CREATE INDEX idx_orders_status
ON orders(status);

CREATE INDEX idx_orders_created_at
ON orders(created_at);

Questi possono aiutare query come:

WHERE status = 'paid'

e:

ORDER BY created_at DESC

Non creare indici alla cieca.

Qui hanno senso perché queste colonne probabilmente compariranno in join, filtri o ordinamenti.

Gli indici devono seguire query reali.

Non vibrazioni.

PostgreSQL non ottimizza le vibrazioni.

Ne abbiamo già parlato.

Resta comunque tragico.

Controllare una query con EXPLAIN ANALYZE

Analizziamo una query.

EXPLAIN ANALYZE
SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid'
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Cerca parole come:

Seq Scan
Index Scan
Hash Join
Nested Loop
Planning Time
Execution Time

Non andare nel panico se l’output sembra complicato.

L’obiettivo non è capire ogni dettaglio adesso.

L’obiettivo è iniziare a leggere il piano.

PostgreSQL ti sta dicendo come lavora.

Come un meccanico che apre il motore.

All’inizio fa paura.

Poi piano piano inizia ad avere senso.

Poi quello che fa paura diventi tu.

In senso buono.

Aggiornare lo stock dopo un ordine

Nei negozi reali, quando un ordine viene pagato, lo stock dovrebbe diminuire.

Per esempio, se l’ordine 1 ha venduto:

Laptop x 1
Mouse x 2
Keyboard x 1

Possiamo aggiornare lo stock manualmente:

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 1;

UPDATE products
SET stock_quantity = stock_quantity - 2
WHERE id = 2;

UPDATE products
SET stock_quantity = stock_quantity - 1
WHERE id = 3;

Poi controlla:

SELECT
  name,
  stock_quantity
FROM products
WHERE id IN (1, 2, 3);

Funziona.

Ma gli aggiornamenti manuali sono pericolosi.

Nelle applicazioni reali, questa cosa deve essere gestita con attenzione.

Di solito dentro una transazione.

Parleremo più avanti delle modifiche sicure al database.

Perché i database sono potenti.

E potenza più distrazione uguale disastri interessanti.

Una query migliore per aggiornare lo stock

Possiamo aggiornare lo stock partendo dai dettagli ordine.

Per l’ordine 1:

UPDATE products AS p
SET stock_quantity = p.stock_quantity - oi.quantity
FROM order_items AS oi
WHERE p.id = oi.product_id
AND oi.order_id = 1;

Questo aggiorna tutti i prodotti dell’ordine 1 in una sola query.

Molto utile.

Molto potente.

Anche pericoloso se usi l’order_id sbagliato.

PostgreSQL obbedirà.

Non è tua madre.

Non ti chiederà:

Sei sicuro, caro?

Quindi controlla sempre le condizioni.

Trovare dettagli ordine per un cliente

Mostra tutti i prodotti degli ordini pagati di Anna:

SELECT
  c.name AS customer_name,
  o.id AS order_id,
  p.name AS product_name,
  oi.quantity,
  oi.unit_price,
  oi.quantity * oi.unit_price AS item_total
FROM customers AS c
JOIN orders AS o
ON c.id = o.customer_id
JOIN order_items AS oi
ON o.id = oi.order_id
JOIN products AS p
ON oi.product_id = p.id
WHERE c.email = 'anna@example.com'
AND o.status = 'paid'
ORDER BY o.id;

Questo è il tipo di query che potresti usare per:

Storico ordini del cliente.
Fatture.
Pannelli admin.
Assistenza clienti.

Molto reale.

Molto utile.

Molto “per favore dimmi cosa ha comprato questo cliente”.

Cercare prodotti per categoria e prezzo

Mostra prodotti della categoria Electronics con prezzo sotto 100:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
WHERE c.name = 'Electronics'
AND p.price < 100
ORDER BY p.price ASC;

Questa query potrebbe alimentare un filtro in un negozio online.

Categoria.

Prezzo.

Ordinamento.

Molto normale.

Molto comune.

Ottimo punto dove pensare agli indici se la tabella cresce.

Aggiungere un indice su più colonne

Se filtri spesso i prodotti per categoria e li ordini per prezzo, questo indice può aiutare:

CREATE INDEX idx_products_category_price
ON products(category_id, price);

Questo indice è utile per query come:

WHERE category_id = 1
ORDER BY price ASC

L’ordine delle colonne conta.

L’indice inizia con category_id.

Poi price.

Non è casuale.

È basato su come funziona la query.

Gli indici non sono adesivi.

Sono strategia.

Piccola strategia da database.

Come gli scacchi, ma con più fatture.

Errori comuni nei piccoli progetti database

Salvare tutto in una sola tabella

Cattiva idea:

customer_name
customer_email
product_name
category_name
order_date
quantity
price

Tutto in una tabella.

Sembra facile.

All’inizio.

Poi cambia l’email del cliente.

Poi cambia il nome della categoria.

Poi cambia il prezzo del prodotto.

Poi i report diventano strani.

Poi qualcuno apre Excel.

Poi la società crolla.

Usa tabelle collegate.

Questo è il senso dei database relazionali.

Dimenticare le chiavi esterne

Senza chiavi esterne, i dati possono diventare incoerenti.

Per esempio:

Un dettaglio ordine punta al prodotto 999.
Ma il prodotto 999 non esiste.

Male.

Le chiavi esterne proteggono le relazioni.

PostgreSQL può fare da guardiano.

Lascialo lavorare.

Dimenticare i prezzi storici

Se i dettagli ordine puntano solo ai prodotti e non salvano unit_price, gli ordini vecchi potrebbero cambiare quando cambia il prezzo del prodotto.

Di solito è male.

Un vecchio ordine deve ricordare il prezzo al momento dell’acquisto.

La storia conta.

Soprattutto quando ci sono soldi di mezzo.

Contare ordini annullati come ricavi

Questo è un errore classico nei report.

Pensa sempre allo stato.

WHERE o.status = 'paid'

Senza questo, il report dei ricavi potrebbe includere ordini annullati o non pagati.

Il grafico sembra più bello.

La realtà no.

La realtà di solito vince.

Usare INNER JOIN quando serve LEFT JOIN

Se vuoi mostrare tutti i clienti, anche quelli senza ordini, usa LEFT JOIN.

Se usi INNER JOIN, i clienti senza ordini spariscono.

A volte è corretto.

A volte rovina silenziosamente il report.

SQL non urla.

Restituisce esattamente quello che hai chiesto.

Anche se hai chiesto male.

Molto onesto.

Molto pericoloso.

Pratica

Mostra tutti i prodotti con categorie:

SELECT
  p.name AS product_name,
  p.price,
  c.name AS category_name
FROM products AS p
JOIN categories AS c
ON p.category_id = c.id
ORDER BY c.name, p.name;

Mostra tutti gli ordini con clienti:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  o.status,
  o.created_at
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
ORDER BY o.id;

Calcola i totali per ogni ordine:

SELECT
  o.id AS order_id,
  c.name AS customer_name,
  SUM(oi.quantity * oi.unit_price) AS order_total
FROM orders AS o
JOIN customers AS c
ON o.customer_id = c.id
JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY o.id, c.name
ORDER BY order_total DESC;

Calcola il ricavo totale pagato:

SELECT
  SUM(oi.quantity * oi.unit_price) AS total_revenue
FROM orders AS o
JOIN order_items AS oi
ON o.id = oi.order_id
WHERE o.status = 'paid';

Mostra la spesa totale per cliente:

SELECT
  c.name AS customer_name,
  COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS total_spent
FROM customers AS c
LEFT JOIN orders AS o
ON c.id = o.customer_id
AND o.status = 'paid'
LEFT JOIN order_items AS oi
ON o.id = oi.order_id
GROUP BY c.name
ORDER BY total_spent DESC;

Mostra le vendite per categoria:

SELECT
  cat.name AS category_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM order_items AS oi
JOIN products AS p
ON oi.product_id = p.id
JOIN categories AS cat
ON p.category_id = cat.id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY cat.name
ORDER BY total_sales DESC;

Mostra prodotti con poco stock:

SELECT
  name,
  stock_quantity
FROM products
WHERE stock_quantity < 10
ORDER BY stock_quantity ASC;

Esegui queste query.

Cambia i dati.

Aggiungi clienti.

Aggiungi ordini.

Aggiungi dettagli ordine.

Rompi qualcosa.

Riparalo.

Questo è imparare.

Molto PostgreSQL.

Molto pratico.

Mini sfida

Estendi il database del negozio.

Aggiungi una nuova tabella chiamata suppliers.

Regole:

• un fornitore può fornire molti prodotti;
• ogni prodotto può avere un fornitore;
• il nome del fornitore è obbligatorio;
• l’email del fornitore deve essere unica.

Crea la tabella:

CREATE TABLE suppliers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

Inserisci i fornitori:

INSERT INTO suppliers (name, email)
VALUES
  ('Tech Supplier', 'tech@example.com'),
  ('Office Supplier', 'office@example.com'),
  ('Home Supplier', 'home@example.com');

Aggiungi una colonna supplier_id ai prodotti:

ALTER TABLE products
ADD COLUMN supplier_id INTEGER REFERENCES suppliers(id);

Aggiorna i prodotti:

UPDATE products
SET supplier_id = 1
WHERE category_id = 1;

UPDATE products
SET supplier_id = 2
WHERE category_id = 3;

UPDATE products
SET supplier_id = 3
WHERE category_id IN (2, 4);

Ora scrivi query per:

• mostrare prodotti con nome categoria e nome fornitore;
• contare prodotti per fornitore;
• trovare fornitori che hanno prodotti con poco stock;
• calcolare vendite per fornitore;
• mostrare fornitori i cui prodotti hanno generato più di 500 in vendite pagate.

Suggerimento per le vendite per fornitore:

SELECT
  s.name AS supplier_name,
  SUM(oi.quantity * oi.unit_price) AS total_sales
FROM suppliers AS s
JOIN products AS p
ON s.id = p.supplier_id
JOIN order_items AS oi
ON p.id = oi.product_id
JOIN orders AS o
ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY s.name
ORDER BY total_sales DESC;

Questa sfida aggiunge un altro livello realistico.

Fornitori.

Prodotti.

Vendite.

Report.

Il database cresce.

Il cervello soffre un po’.

È normale.

La crescita ha un prezzo.

Anche i JOIN.

Riassunto

Oggi hai costruito un progetto pratico con PostgreSQL.

Hai imparato a:

• progettare un piccolo database per un negozio;
• creare tabelle collegate;
• usare chiavi primarie;
• usare chiavi esterne;
• separare i dati in strutture pulite;
• salvare prezzi storici in order_items;
• inserire dati di esempio pratici;
• leggere dati collegati con JOIN;
• calcolare totali ordine;
• calcolare ricavi;
• contare ordini per cliente;
• calcolare la spesa dei clienti;
• calcolare vendite per categoria;
• trovare prodotti con poco stock;
• creare indici utili;
• pensare a problemi reali di reportistica.

Questo è un grande passo.

Ora PostgreSQL non è più solo teoria.

Lo hai usato come uno strumento reale.

Un database non è solo tabelle.

È struttura.

Relazioni.

Regole.

Domande.

Risposte.

E a volte errori strani che ti fanno mettere in discussione le tue scelte di vita.

Ma fa parte del viaggio.

Prossima lezione

Nella prossima lezione vedremo come PostgreSQL lavora con le applicazioni.

Parleremo di come le applicazioni backend si collegano a PostgreSQL.

Vedremo le connection string.

Le variabili d’ambiente.

Idee base di sicurezza.

E perché mettere le password del database direttamente nel codice è una pessima idea.

Una pessima idea davvero.

Il tipo di idea che sveglia di notte le persone della sicurezza.