Nella lezione precedente, dopo esserci connessi con MySQL creando una nuova istanza di mysqli, abbiamo sfruttato il metodo query() di MySQLi per creare il database della nostra applicazione di esempio e per selezionarlo come ambiente di lavoro.
Con lo stesso metodo abbiamo poi impostato la struttura delle tabelle che ci occorreranno per la gestione di una biblioteca. Vediamo ora nel dettaglio quali campi abbiamo creato per ciascuna tabella, e come questi interagiranno fra loro nell'esecuzione delle query che programmeremo per la nostra applicazione utilizzando la libreria MySQLi.
Prevediamo naturalmente l'esistenza di un backend, ed è quindi necessario predisporre una tabella per gestire le credenziali di accesso. Per la tabella login abbiamo impostato tre campi:
| Campo | Descrizione |
|---|---|
| id | Un campo numerico che funge da chiave primaria, che verrà incrementato automaticamente da MySQL ad ogni nuovo inserimento (lo inseriremo in ogni tabella). |
| user | Un campo alfanumerico per il nome dell'utente, di lunghezza massima di 40 caratteri. |
| password | Un campo alfanumerico di 64 caratteri per la password dell'utente. Nello specifico non verrà memorizzata la password, bensì il suo hash di 64 caratteri generato tramite l'algoritmo SHA256. |
Per la tabella dei libri, in pratica il nostro catalogo, abbiamo costruito questa struttura:
| Campo | Descrizione |
|---|---|
| id | Vale quanto già specificato per tutte le tabelle. |
| autore | Un campo alfanumerico di 40 caratteri per l'autore del libro. |
| editore | Un campo alfanumerico di 40 caratteri per l'editore del libro. |
| titolo | Un campo testuale dalla lunghezza non definita per il titolo. |
| anno | Un campo numerico per l'anno di pubblicazione. Ha un range molto ridotto per il quale è sufficiente il tipo smallint. |
Per gli utenti, invece, dovremmo costruire una piccola anagrafica:
| Campo | Descrizione |
|---|---|
| id | Vale quanto già specificato per tutte le tabelle. |
| nome | Un campo alfanumerico di 40 caratteri per il nome dell'utente. |
| cognome | Un campo alfanumerico di 40 caratteri per il cognome. |
| indirizzo | Un campo testuale dalla lunghezza non definita per l'indirizzo. |
| data | Un campo di tipo date per la data di nascita. |
Da un punto di vista strutturale la tabella più interessante è quella dei prestiti, per la sua funzione essa dovrà relazionarsi sia con la tabella degli utenti che con quella dei libri, e lo farà utilizzando i rispettivi id. In pratica ogni record rappresenta un prestito.
| Campo | Descrizione |
|---|---|
| id | Vale quanto già specificato per tutte le tabelle. |
| id_utente | Un campo numerico utilizzato per riferirsi all'id dell'utente che ha al suo attivo il prestito. |
| id_libro | Un campo numerico utilizzato per riferirsi all'id del libro prestato. |
| data | Un campo di tipo date per l'inizio del prestito. |
| restituito | Un campo di tipo enum che può contenere solo i valori 0 e 1: in pratica un flag che indica se il libro è stato riconsegnato o meno. |
Le relazioni fra le tabelle
All'interno della nostra applicazione dovremo svolgere diversi compiti tramite i dati con i quali popoleremo il database.
Useremo la tabella login per verificare le credenziali degli operatori; inseriremo, modificheremo o elimineremo i record delle tabelle utenti e libri, e potremo anche interrogarle sulla base di diversi parametri (ad esempio, per sapere quanti e quali utenti maggiorenni, o quanti e quali libri sono stati pubblicati da un certo editore o in un certo anno); gestiremo i prestiti tenendo in adeguata considerazione le relazioni che legano questa tabella con le altre.
Ogni prestito sarà in relazione con un solo utente e un solo libro, ma uno stesso utente può avere in prestito più di un libro, e uno stesso libro può essere stato prestato, nel corso del tempo, a più utenti. Con le query adeguate la tabella dei prestiti può dirci quali e quanti utenti hanno dei prestiti in corso, se sono scaduti (fissandone una durata), se un libro è disponibile o ancora in prestito, o quanti prestiti ha in corso un singolo utente (volendo magari limitare il numero dei volumi a disposizione per ogni utente).
Nella prossima lezione vedremo in che modo inserire dei record nelle nostre tabelle, e come compiere le prime estrazioni di dati.