05 PHP e MySQL XP

05 PHP e MySQL XP
14-12-2004
5
0:05
Pagina 75
Progettazione
di database relazionali
Sin dal Capitolo 2, Primi passi con MySQL, abbiamo lavorato con un database molto semplice che era composto da una singola tabella chiamata “joke”. Anche se questo database ci è servito come introduzione ai database MySQL c’è molto di più di quanto possa insegnare questo piccolo esempio, per quanto riguarda i database relazionali. In questo capitolo approfondiremo e impareremo nuove caratteristiche di MySQL, per cercare di capire e sfruttare tutto
quello che i database relazionali hanno da offrire.
Tenete presente, però, che molti argomenti verranno trattati in modo informale e non rigoroso. Come alcuni esperti di Scienza del Computer potranno confermarvi, la progettazione di
database è un’importante area di ricerca con principi testati e provati matematicamente e che,
anche se utili, sono aldilà degli obiettivi di questo testo. Se volete maggiori informazioni consultate il sito www.datamodel.org per un elenco di ottimi libri e per avere altre risorse utili. In
particolare potreste dare un’occhiata alle Rules of Data Normalization nella sezione “Data
Modeling” del sito.
Dare a Cesare quel che è di Cesare
Per iniziare, ricordiamoci com’era la struttura della nostra tabella di barzellette contenente tre
colonne: id, joketext e jokedate. Insieme, queste colonne ci permettono di identificare le barzellette (id), di mantenere una traccia del loro testo (joketext) e della data in cui sono state
inserite (jokedate). Il codice SQL che crea questa tabella e che inserisce un paio di record è nell’archivio del codice con il nome di jokes1.sql.
Diciamo ora di voler indicare altre informazioni sulle nostre barzellette: il nome delle persone che le hanno inserite. Sembrerebbe naturale voler aggiungere una nuova colonna alla
nostra tabella per fare ciò. Il comando SQL ALTER TABLE (che prima non abbiamo visto) ci permette di fare esattamente ciò. Connettetevi al vostro server MySQL usando la riga di comando mysql proprio come nel Capitolo 2, Primi passi con MySQL, selezionate il vostro database
(ijdb, se avete usato il nome suggerito in quel capitolo) e quindi digitate questo comando:
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 76
Creare siti Web con PHP e MySQL
mysql>ALTER TABLE joke ADD COLUMN
->authorname VARCHAR(255);
Questo codice aggiunge una colonna chiamata authorname alla vostra tabella. Il tipo dichiarato è una stringa a lunghezza variabile fino a 255 caratteri – un sacco di spazio, adatto a nomi
anche molto esotici. Aggiungiamo una colonna per gli indirizzi e-mail degli autori:
mysql>ALTER TABLE joke ADD COLUMN
->authoremail VARCHAR(255);
Per maggiori informazioni sul comando ALTER TABLE andate all’Appendice A, Sintassi MySQL.
Per essere sicuri che le due colonne siano state aggiunte in modo corretto dovremmo chiedere a MySQL di descriverci la tabella:
mysql>DESCRIBE joke;
+-------------+--------------+------+-----+-------------+
| Field
| Type
| Null | Key | Default
|
+-------------+--------------+------+-----+-------------+
| id
| int(11)
|
| PRI | NULL
|
| joketext
| text
| YES |
| NULL
|
| jokedate
| date
|
|
| 0000-00-00 |
| authorname | varchar(255) | YES |
| NULL
|
| authoremail | varchar(255) | YES |
| NULL
|
+-------------+--------------+------ +-----+-------------+
5 rows in set (0.01 sec)
Sembra a posto, no? Ovviamente dovremmo fare dei cambiamenti al codice HTML e PHP dei
form che abbiamo creato nel Capitolo 4, Pubblicare i dati MySQL sul Web, quelli che ci permettevano di aggiungere nuove barzellette al database. Usando la query UPDATE potremmo
aggiungere i dettagli degli autori per tutte le barzellette nella tabella. Ma, prima di essere travolti da questi cambiamenti, dobbiamo fermarci e considerare se questo nuova struttura di
database è la scelta giusta in questo caso. Pare proprio di no.
Mantenere le cose separate
All’ingrandirsi dei siti Web basati su database potreste decidere che un elenco personale di
barzellette non è abbastanza. Infatti potreste volere ricevere più barzellette inviate che quelle
che immettete voi. Diciamo che decidete di pubblicare un sito dove le persone, da tutte le parti
del mondo, possono condividere barzellette fra loro. Avete mai sentito parlare dell’Internet
Movie Database (IMDB)? Voi decidete di aprire l’Internet Joke Database (IJDB)! Aggiungere
il nome dell’autore e il suo indirizzo e-mail sicuramente ha senso, ma il metodo che abbiamo
usato sopra porta potenzialmente a diversi problemi:
•
76
Cosa succedere se un’affezionata contribuente del vostro sito, che si chiama Joan Smith,
cambia il suo indirizzo e-mail? Potrebbe iniziare a inserire nuove barzellette usando il
nuovo indirizzo, ma quello vecchio rimarrebbe associato alle barzellette inviate nel passato. Guardando il vostro database potreste semplicemente pensare che esistono due perso-
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 77
Capitolo 5: Progettazione di database relazionali
•
ne che si chiamano Joan Smith che inviano delle barzellette. Se lei fosse particolarmente
coscienziosa potrebbe informarvi del cambiamento di indirizzo e voi potreste cercare di
aggiornare tutte le vecchie barzellette con il nuovo indirizzo; se solo ne saltaste una, però,
il database conterrebbe delle informazioni non corrette. Gli esperti di progettazione del
database si riferiscono a questo genere di problema come un’anomalia di aggiornamento.
Sarebbe naturale per voi fare affidamento sul vostro database per fornire un elenco di persone che non hanno mai mandato barzellette al vostro sito. Ed effettivamente potreste
ottenere facilmente un elenco usando la seguente query:
mysql>SELECT DISTINCT authorname, authoremail
->FROM joke;
•
La parola DISTINCT in questa query dice a MySQL di non duplicare i risultati delle righe
nell’output. Per esempio, se Joan Smith ha inviato 20 barzellette sul vostro sito, il suo
nome dovrebbe apparire 20 volte nell’elenco, anziché solo una, se non provvedeste a usare
l’opzione DISTINCT.
Se, per qualche ragione, decidete di rimuovere tutte le barzellette che una certa persona
vi ha inviato, rimuovereste con esse tutti i record di questa persona dal database nel processo e non sareste più in grado di inviargli informazioni sul vostro sito. Poiché la mailing list potrebbe essere una fonte di entrate primaria per il vostro sito non penso siate
dell’idea di gettare un indirizzo e-mail solo perché non vi piacciono le barzellette che questa persona vi ha inviato. Gli esperti di progettazione la chiamano anomalia di cancellazione.
Non avete garanzie che Joan Smith non inserisca il suo nome come “Joan Smith” un giorno, “J. Smith” un altro e come “Smith, Joan” ancora in un’altra occasione. Questo renderebbe sempre più difficile mantenere la traccia di un autore specialmente se Joan Smith
avesse diversi indirizzi e-mail.
Questi problemi – e altri – possono essere risolti velocemente con delle query. Anziché immagazzinare le informazione degli autori nella tabella delle barzellette, però, possiamo creare una
nuova tabella dedicata completamente al nostro elenco di autori. Poiché abbiamo usato una
colonna chiamata id nella tabella delle barzellette, per identificare ogni barzelletta con un
numero univoco, useremo una colonna chiamata allo stesso modo per identificare gli autori.
Possiamo allora usare gli id degli autori nella nostra tabella delle barzellette per associare gli
autori alle loro barzellette. Il layout completo del database è mostrato nella figura 5.1.
Quello che mostrano le due tabelle sono tre barzellette e due autori. La colonna authorid della
tabella joke fornisce una relazione tra le due tabelle, indicando che Kevin Yank ha inviato le
barzellette 1 e 2 e Joan Smith invece la numero 3. Da notare che, poiché ogni autore appare
solo una volta nel database e appare indipendentemente dalle barzellette che ha inviato, abbiamo evitato i problemi delineati in precedenza.
La caratteristica più importante della progettazione di questo database, comunque, è che poiché stiamo immagazzinando informazioni su due tipi di “oggetti” (barzellette e autori) è più
appropriato avere due tabelle. Questa è una regola che dovreste sempre tenere a mente quan77
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 78
Creare siti Web con PHP e MySQL
Figure 5.1. Il campo authorid associa ogni riga in “joke” con una riga in “author”.
do progettate un database: ogni tipo di entità (o “oggetto”) di cui volete immagazzinare informazioni dovrebbe avere la sua tabella separata.
Preservare il database di cui sopra sin dall’inizio è abbastanza semplice (e coinvolge solo due
query del tipo CREATE TABLE ), ma, poiché ci piacerebbe fare questi cambiamenti in un modo
non-distruttivo (per esempio senza perdere nessuna preziosa barzelletta), useremo di nuovo
il comando ALTER. Prima di tutto dobbiamo sbarazzarci delle colonne relazionate all’autore
nella tabella delle barzellette :
mysql>ALTER TABLE joke DROP COLUMN authorname;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>ALTER TABLE joke DROP COLUMN authoremail;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
Adesso creiamo la nostra nuova tabella:
mysql>CREATE TABLE author (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(255),
-> email VARCHAR(255)
->);
Per finire, aggiungiamo la colonna authorid alla nostra tabella delle barzellette:
mysql>ALTER TABLE joke ADD COLUMN authorid INT;
78
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 79
Capitolo 5: Progettazione di database relazionali
Se preferite i comandi CREATE TABLE per creare le due tabelle, sin dal principio sono forniti nell’archivio del codice 2tables.sql.
Quello che ci rimane da fare è aggiungere alcuni autori alla nuova tabella e assegnare gli autori a tutte le barzellette esistenti nel database, completando la colonna authorid[1]. Proseguite
in questo lavoro, se vi piace – dovrebbe farvi fare pratica con le query INSERT e UPDATE.
Gestire tabelle multiple
Con i dati in due tabelle potrebbe sembrare che state complicando il processo per il loro recupero. Considerate, per esempio, il nostro scopo originale: mostrare un elenco di barzellette
con il nome e l’indirizzo e-mail dell’autore a fianco di ogni barzelletta. Nella soluzione con
una singola tabella potreste avere tutte le informazioni di cui avete bisogno per far un tale
elenco, utilizzando una sola dichiarazione SELECT nel vostro codice PHP:
$jokelist = mysql_query(
“SELECT joketext, authorname, authoremail FROM jokes”);
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke[‘joketext’];
$name = $joke[‘authorname’];
$email = $joke[‘authoremail’];
// Mostra la barzelletta con l’informazione sull’autore
echo “<p>$joketext<br />” .
“(by <a href=’mailto:$email’>$name</a>)</p>”;
}
Con il primo sistema, a prima vista, non sembra possibile. Poiché i dettagli di ogni barzelletta non sono conservati nella tabella delle barzellette, potreste pensare che occorra recuperare
questi dati separatamente per ogni barzelletta che volete mostrare. Il codice per fare quest’azione sarebbe il seguente:
// Recupera l’elenco delle barzellette
$jokelist = mysql_query(“SELECT joketext, authorid FROM joke”);
while ($joke = mysql_fetch_array($jokelist)) {
// Recupera il testo e l’authorid per la barzelletta
$joketext = $joke[‘joketext’];
$authorid = $joke[‘authorid’];
// Recupera i dettagli dell’autore per la barzelletta
$authordetails = mysql_query(
“SELECT name, email FROM author WHERE id=$authorid”);
$author = mysql_fetch_array($authordetails);
[1]
Per adesso dovrete fare ciò manualmente. Ma non preoccupatevi, nel Capitolo 6, Un Content Management
System vedremo come PHP possa automaticamente inserire record con gli id corretti, riportando la relazione che
c’è fra loro.
79
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 80
Creare siti Web con PHP e MySQL
$name = $author[‘name’];
$email = $author[‘email’];
// Mostra la barzelletta con l’informazione sull’autore
echo “<p>$joketext<br />” .
“(by <a href=’mailto:$email’>$name</a>)</p>”;
}
È piuttosto confuso e comporta una query al database per ogni singola barzelletta che viene
mostrata, cosa che potrebbe rallentare considerevolmente il caricamento della vostra pagina.
Considerato tutto ciò sembrerebbe che il vecchio metodo sia la soluzione migliore, nonostante la sua debolezza. Fortunatamente i database relazionali come MySQL sono progettati per
semplificare il lavoro con i dati salvati in tabelle multiple! Usando una nuova formulazione
della dichiarazione SELECT chiamata unione (join), potete avere il meglio di entrambi i mondi.
Il join vi permette di trattare i dati relazionati in tabelle multiple come se fossero immagazzinati in una tabella unica. Questa è la sintassi in un caso semplice:
mysql>SELECT columns FROM tables
->WHERE condition(s) for data to be related;
Nel vostro caso le colonne alle quali siete interessati sono joketext nella tabella delle barzellette e nome ed email nella tabella dell’autore. La condizione necessaria a un record nella tabella delle barzellette, affinché sia collegato a un record nella tabella degli autori, è che il valore
nella colonna authorid nella tabella delle barzellette sia uguale al valore a quello della colonna id nella tabella degli autori. Ecco un esempio di un join (le prime due query mostrano semplicemente cosa è contenuto nelle due tabelle – non sono necessarie):
mysql>SELECT LEFT(joketext, 20), authorid FROM joke;
+---------------------- +-----------+
| LEFT(joketext, 20)
| authorid |
+---------------------- +-----------+
| Why did the chicken |
1 |
| Knock knock. Who’s t |
1 |
| A man walks into a b |
2 |
+-----------------------+----------+
3 rows in set (0.00 sec)
mysql>SELECT * FROM author;
+---- +------------ +---------------------+
| id | name
| email
|
+---- +------------ +---------------------+
| 1 | Kevin Yank | [email protected] |
| 2 | Joan Smith | [email protected]
|
+---- +------------ +---------------------+
2 rows in set (0.00 sec)
80
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 81
Capitolo 5: Progettazione di database relazionali
mysql>SELECT LEFT(joketext, 20), name, email
->FROM joke, author WHERE authorid = author.id;
+-----------------------+------------ +---------------------+
| LEFT(joketext, 20)
| name
| email
|
+-----------------------+------------ +---------------------+
| Why did the chicken
| Kevin Yank | [email protected] |
| Knock-knock! Who’s t | Kevin Yank | [email protected] |
| A man walks into a b | Joan Smith | [email protected]
|
+-----------------------+------------ +---------------------+
3 rows in set (0.00 sec)
Vedete? Il risultato del terzo SELECT, che è un join, raggruppa i valori immagazzinati nelle due
tabelle in una unica tabella di risultati, che ha relazionato correttamente i dati che appaiono
insieme. Anche se i dati sono immagazzinati in due tabelle, potete sempre ottenere le informazioni di cui avete bisogno per emettere un elenco di barzellette sulla vostra pagina Web con
una singola query. Da notare, nella query, che, poiché ci sono colonne chiamate id in entrambe le tabelle, si è dovuto specificare il nome della tabella per riferirsi alla colonna id della tabella degli autori (author.id). Se non specificate il nome della tabella, allora MySQL non saprà a
quale id si sta riferendo e incapperete nel seguente errore:
mysql>SELECT LEFT(joketext, 20), name, email
->FROM joke, author WHERE authorid = id;
ERROR 1052: Column: ‘id’ in where clause is ambiguous
Ora che sapete come accedere in modo efficiente ai dati salvati nelle vostre due tabelle, potete riscrivere il codice per il vostro elenco di barzellette sfruttando le unioni:
Esempio 5.1. jokelist2.php (esercitazione)
$jokelist = @mysql_query(
‘SELECT joketext, name, email
FROM joke, author WHERE authorid=author.id’);
if (!$jokelist) {
exit(‘<p>Error performing query: ‘ . mysql_error() . ‘</p>’);
}
while ($joke = mysql_fetch_array($jokelist)) {
$joketext = $joke[‘joketext’];
$name = $joke[‘name’];
$email = $joke[‘email’];
// Mostra la barzelletta con l’informazione dell’autore
echo “<p>$joketext<br />” .
“(by <a href=’mailto:$email’>$name</a>)</p>”;
}
Il risultato è mostrato nella figura 5.2.
Più lavorate con i database, più comprenderete quanto sia potente poter combinare dei dati
contenuti in tabelle separate in un’unica tabella. Considerate, per esempio, la seguente query
che mostra un elenco di tutte le barzellette scritte da Joan Smith:
81
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 82
Creare siti Web con PHP e MySQL
Figura 5.2. Le migliori le ho scritte tutte io!
mysql>SELECT joketext FROM joke, author WHERE
->name=”Joan Smith” AND authorid=author.id;
+----------------------------------+
| joketext
|
+----------------------------------+
| A man walks into a bar. “Ouch.” |
+----------------------------------+
1 row in set (0.02 sec)
I risultati della precedente query provengono solo dalla tabella delle barzellette, ma la query
usa un join per cercare barzellette secondo un valore immagazzinato nella tabella degli autori. Ci saranno molte altri esempi intelligenti di query come queste per tutto il libro, ma questo esempio, da solo, illustra che le applicazioni pratiche dei join sono molte e che, quasi sempre, possono evitarvi un sacco di lavoro!
Relazioni semplici
La progettazione migliore per un database è dettata di solito dal tipo di relazione che intercorre tra i dati. In questo esempio esamineremo i più frequenti tipi di relazioni e spiegheremo come meglio rappresentarli in un database relazionale.
Nel caso di una semplice relazione uno-a-uno tutto quello di cui avrete bisogno sarà una singola tabella. Un esempio di relazione uno a uno che avete visto finora è il database con l’indirizzo e-mail di ogni autore, nel nostro database delle barzellette. Poiché ci sarà un indirizzo email per ogni autore e un autore per ogni indirizzo e-mail, non c’è ragione per dividere gli
indirizzi in tabelle separate.
82
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 83
Capitolo 5: Progettazione di database relazionali
Una relazione molti-a-uno è un po’ più complicata, ma ne avete vista una anche di questo tipo.
Ogni barzelletta nel nostro database è associata con un solo autore, anche se molte sono state
scritte dallo stesso autore. Questa relazione barzelletta-autore è del tipo molti-a-uno. Abbiamo
già trattato i problemi che ci sono nel conservare le informazioni associate all’autore della barzelletta nella stessa tabella della barzelletta. In breve, possono risultare molte copie dello stesso dato che sono poi difficili da mantenere sincronizzate, sprecando spazio. Se dividiamo i
dati in due tabelle e utilizziamo una colonna id per collegarle insieme, il che è reso possibile
dai join come mostrato prima, tutti questi problemi scompariranno.
Una relazione uno-a-molti è semplicemente una relazione molti-a-uno vista dalla direzione
opposta. Fintanto che la relazione barzelletta-autore è molti-a-uno, la relazione autore-barzelletta è uno-a-molti (c’è un autore potenzialmente per molte barzellette). Questo è facile da
vedere in teoria, ma quando vi incappate non è poi così ovvio. Nel caso di barzellette e autori, abbiamo iniziato con un elenco di barzellette (i molti) e quindi abbiamo voluto assegnare
un autore a ognuno di loro (l’uno). Adesso guardiamo a un ipotetico problema di progettazione se iniziamo dall’uno e vogliamo aggiungere i molti.
Diciamo che vogliamo permettere a ognuno degli autori nel nostro database (l’uno) di avere
indirizzi e-mail multipli (i molti). Quando qualcuno senza esperienza nella progettazione di
database si avvicina a una relazione uno-a-molti come questa, il suo primo pensiero è spesso
quello di cercare di immagazzinare valori multipli in un singolo campo di database, come
mostrato nella figura 5.3.
Figura 5.3. Mai sovraccaricare il campo di una tabella per immagazzinare valori multipli, come
viene fatto qui.
Mentre questo potrebbe funzionare per recuperare un singolo indirizzo e-mail da un database, in questo caso avremmo bisogno di spezzare la stringa nel cercare le virgole (o qualsiasi
altro carattere speciale abbiate scelto come separatore) – un’operazione non tanto semplice e
potenzialmente molto onerosa in termini di tempo. Provate a immaginare un codice PHP
necessario per rimuovere uno specifico indirizzo e-mail da un autore in particolare! In più
dovrete permettere valori molto più lunghi nella colonna e-mail, il che causerebbe uno spreco di spazio sul disco perché la maggior parte degli autori avrebbe comunque un solo indirizzo e-mail.
83
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 84
Creare siti Web con PHP e MySQL
Fate un passo indietro per rendervi conto che questa relazione uno-a-molti è la stessa relazione molti-a-uno che abbiamo visto tra barzellette e autori. La soluzione, quindi, è la medesima:
dividete gli “oggetti” (in questo caso, indirizzi e-mail) nelle loro tabelle. La struttura di database risultante è mostrata nella figura 5.4.
Figura 5.4. Il campo authorid associa ogni riga di email con una riga degli autori.
Usando un join con questa struttura possiamo facilmente avere un elenco degli indirizzi email associati con un particolare autore:
mysql>SELECT email FROM author, email WHERE
->name=”Kevin Yank” AND authorid=author.id;
+-----------------------+
| email
|
+-----------------------+
| [email protected]
|
| [email protected] |
+-----------------------+
2 rows in set (0.00 sec)
Relazioni molti-a-molti
Ora avete pubblicato sul vostro sito Web un database di barzellette che cresce vigorosamente.
Sta crescendo così velocemente, infatti, che il numero di barzellette non è più gestibile. Le persone che visitano il sito hanno a che fare con una pagina gigantesca che contiene centinaia di
barzellette senza alcun tipo di struttura. Qualcosa deve cambiare.
84
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 85
Capitolo 5: Progettazione di database relazionali
Decidete di divider le vostre barzellette in categorie come “Knock-knock” , “Cross the road”,
“Lawyers” e “Political”. Ricordandoci la nostra regola di base citata precedentemente, dovete
identificare le categorie di barzellette come un differente tipo di “oggetto” e creare una nuova
tabella per esso:
mysql>CREATE TABLE category (
-> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(255)
->);
Query OK, 0 rows affected (0.00 sec)
Siete arrivati al compito ingrato di assegnare categorie alle vostre barzellette. Capita che una
barzelletta “politica” possa essere anche una barzelletta sull’“attraversamento di strada” e una
barzelletta sul “knock-knock” possa anche riguardare gli avvocati. Una singola barzelletta
potrebbe appartenere a più categorie e ogni categoria conterrà molte barzellette. Questa è una
relazione molti-a-molti.
Molti sviluppatori inesperti, di nuovo, cominceranno a pensare a dei modi per immagazzinare diversi valori in una singola colonna, perché la soluzione ovvia è quella di aggiungere una
colonna di categoria alla tabella delle barzellette e usarla per fare un elenco degli id di quelle
categorie alle quali ogni barzelletta appartiene. Una seconda regola di base potrebbe essere
utile: se avete bisogno di immagazzinare valori multipli in un singolo campo, la vostra progettazione è probabilmente difettosa.
Il modo corretto di rappresentare una relazione molti-a-molti è quello di usare una tabella di
lookup. Questa è una tabella che non contiene alcun dato reale, ma che fa un elenco delle coppie che sono relazionate. La figura 5.5 mostra come dovrebbe apparire la progettazione di
database per le nostre categorie di barzellette.
Figura 5.5. La tabella jokecategory associa coppie di righe dalle tabelle joke e category.
85
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 86
Creare siti Web con PHP e MySQL
La tabella jokecategory associa gli id delle barzellette (jokeid) con gli id delle categorie (categoryid). In questo esempio possiamo vedere che la barzelletta che inizia con “How many
lawyers…” appartiene sia alla categoria “Lawyers” sia a quella “Light bulb”.
Una tabella lookup è creata allo stesso modo in cui è creata ogni altra tabella. La differenza sta
nella scelta della chiave primaria (primary key). Ogni tabella che abbiamo creato fino a qui ha
avuto una colonna chiamata id, che era progettata per essere PRIMARY KEY. Progettare una
colonna come chiave primaria dice a MySQL di non permettere a due record, in quella colonna, di avere lo stesso valore e velocizza anche le operazioni di unione basate su quella colonna.
Nel caso di una tabella di lookup non c’è una singola colonna che vogliamo forzare ad avere
valori univoci. Ogni id di barzelletta appare più di una volta, poiché ogni barzelletta può
appartenere a più di una categoria e ogni id di categoria può apparire più di una volta, in
quanto ogni categoria può contenere molte barzellette. Quello che non vogliamo è che la stessa coppia di valori appaia nella tabella due volte. E, visto che l’unico motivo di questa tabella
è quello di facilitare i join, il beneficio di velocità offerto da una chiave primaria sarebbe molto
utile. Per questa ragione di solito si creano tabelle lookup con chiave primarie a più colonne,
come la seguente:
mysql>CREATE TABLE jokecategory (
-> jokeid INT NOT NULL,
-> categoryid INT NOT NULL,
-> PRIMARY KEY(jokeid, categoryid)
->);
Questo codice crea una tabella in cui le colonne jokeid e categoryid insieme formano la chiave primaria. Ciò rafforza l’univocità che è appropriata per un tabella lookup, prevenendo che
una stessa barzelletta sia assegnata a una categoria particolare più di una volta e velocizzando
i join che fanno uso di questa tabella.
Ora che la vostra tabella lookup è al suo posto e contiene gli assegnamenti della categoria,
potete usare un join per creare diverse query. La query seguente farà un elenco di tutte le barzellette nella categoria “Knock-knock”:
mysql>SELECT joketext
->FROM joke, category, jokecategory
->WHERE name=”Knock-knock” AND
->categoryid=category.id AND jokeid=joke.id;
La query seguente mostra un elenco delle categorie che contengono barzellette che iniziano
per “How many lawyers…”:
mysql>SELECT name
->FROM joke, category, jokecategory
->WHERE joketext LIKE “How many lawyers%”
->AND categoryid=category.id AND jokeid=joke.id;
86
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 87
Capitolo 5: Progettazione di database relazionali
E questa query, che fa anche uso della nostra tabella degli autori per fare un’unione di quattro tabelle, fa un elenco dei nomi di tutti gli autori che hanno scritto barzellette della categoria “Knock-knock”:
mysql>SELECT author.name
->FROM joke, author, category, jokelookup
->WHERE category.name=”Knock-knock”
->AND categoryid=category.id AND jokeid=joke.id
->AND authorid=author.id;
Riepilogo
In questo capitolo abbiamo spiegato i fondamentali di una buona progettazione di database e
abbiamo imparato come MySQL e, per quello che ci interessava, tutti i sistemi di gestione dei
database relazionali forniscono supporto per la rappresentazione di differenti tipi di relazioni tra entità. Dall’iniziale comprensione delle relazioni uno-a-uno abbiamo espanso la nostra
conoscenza fino a includere relazioni molti-a-uno, uno-a-molti e molti-a-molti.
Durante ciò avete appreso nuove caratteristiche di comandi SQL. In particolare avete imparato a usare la query SELECT per fare un’unione di dati sparsi in tabelle multiple in una singola serie di risultati. Nel Capitolo 6, Un Content Management System, imparerete a utilizzare
tutto quello che avete imparato, insieme a nuovi trucchi, per costruire un content management system in PHP. Lo scopo di un sistema del genere è quello di fornire un’interfaccia personalizzata e basata sul Web, che gestisca i contenuti del database, anziché obbligarvi a digitare manualmente tutte le cose dalla riga di comando MySQL.
87
05 PHP e MySQL XP
14-12-2004
0:05
Pagina 88