Esempio: struttura del DB di un albergo

Dopo avere fatto un'ampia carrellata su MySQL, chiudiamo questa guida con
un breve tutorial dedicato ad un ipotetico database per la gestione delle
prenotazioni di un piccolo albergo.

Si tratta di un argomento che presenta una certa complessità, per cui non
avremo certamente la pretesa di esplorarlo in maniera esauriente o -
tanto meno - professionale. Tutto quello che vogliamo fare è disegnare un
piccolo database con qualche query di esempio.

Per rendere le cose un po' meno complesse di come sarebbero in realtà,
faremo qualche assunzione un pochino semplicistica sul modo in cui il
nostro albergo viene gestito. Diciamo quindi che si tratta di un piccolo
hotel in una località marittima, con le stanze distribuite su quattro
piani; ci sono due stanze singole, due doppie, sette matrimoniali e due
triple; tutte le stanze matrimoniali hanno la possibilità di aggiungere
un terzo letto, e tre di queste, più grandi, possono ospitarne anche un
quarto; anche le due stanze triple hanno la possibilità di aggiungere il
quarto letto.

Prevediamo anche alcune caratteristiche che diversificano le camere:
alcune hanno un televisore, alcune hanno la vista sul mare, alcune hanno
l'aria condizionata e, infine, alcune sono riservate ai fumatori.
Tuttavia, come abbiamo detto prima vogliamo semplificare un po' le cose,
per cui stabiliamo che queste caratteristiche non incidono sul prezzo
della camera, che dipende invece esclusivamente dal periodo e dal numero
di posti letto. Prevediamo anche un costo fisso, indipendente dalla
stagione, per i letti aggiunti e per altri supplementi come la culla in
camera; infine decidiamo che il trattamento standard fornito dal nostro
hotel è la mezza pensione, per cui chiederemo un supplemento per la
pensione completa e concederemo una riduzione per chi vuole solo bed
& breakfast; anche queste differenze saranno indipendenti dal periodo
stagionale.

Naturalmente la prima cosa da fare sarà creare il database, che
chiameremo 'hotel':

CREATE DATABASE hotel DEFAULT CHARACTER SET latin1 DEFAULT COLLATE
latin1_general_ci;
USE hotel;

Stabiliamo quindi che il database userà il charset dei caratteri latini
con la relativa collation generica; dopo averlo creato usiamo il comando
USE per farlo diventare il database di default per le istruzioni
successive.

Per prima cosa prevediamo una semplicissima tabella in cui registrare i
dati dei clienti:

CREATE TABLE `clienti` (
  `id` mediumint unsigned NOT NULL auto_increment,
  `nominativo` varchar(100) NOT NULL,
  `indirizzo` varchar(200) NOT NULL,
  `telefono` varchar(15) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB;

Come vedete, memorizziamo in questa tabella solo i dati essenziali:
nominativo, indirizzo e telefono; usiamo un id autoincrementante come
chiave della tabella.

Avremo poi una tabella relativa alle camere:

CREATE TABLE `camere` (
  `numero` smallint unsigned NOT NULL,
  `piano` tinyint unsigned NOT NULL,
  `tipo` enum('singola','doppia','matrimoniale','tripla') NOT NULL,
  `lettiAggiunti` set('terzo','quarto') NOT NULL,
  `optionals` set('fumatori','ariaCondizionata','vistaMare','tv')
                NOT NULL,
   PRIMARY KEY  (`numero`)
) ENGINE=InnoDB;

Utilizziamo quindi il numero della camera come chiave primaria; oltre al
piano a cui è situata la stanza, registriamo il tipo di camera in un
campo di tipo enum, elencando i possibili valori che questo può assumere;
inoltre utilizziamo due campi di tipo set per segnalare gli eventuali
letti che possono essere aggiunti e le caratteristiche opzionali della
stanza; l'uso dei campi set in questo caso è ovviamente dovuto al fatto
che più di uno dei valori elencati possono essere utilizzati
contemporaneamente.

Avremo poi la tabella contenente i prezzi per ogni tipo
di camera, relativamente ai diversi periodi:

CREATE TABLE `prezzi` (
  `periodoDal` date NOT NULL,
  `periodoAl` date NOT NULL,
  `tipoCamera` enum('singola','doppia','matrimoniale','tripla')
                NOT NULL,
  `prezzo` decimal(6,2) NOT NULL
) ENGINE=InnoDB;

In questa tabella utilizziamo due campi di tipo date per indicare
l'inizio e la fine del periodo di validità di ogni tariffa; abbiamo poi
un campo per il tipo di camera, equivalente a quello della tabella
camere, e infine un campo di tipo decimal per il prezzo: stiamo un po'
larghi e prevediamo quattro cifre intere e due decimali.

Prevediamo ora una piccola tabellina per i prezzi dei
supplementi, che, come abbiamo detto, sono indipendenti dalle
stagionalità:

CREATE TABLE `supplementi` (
  `codice` tinyint unsigned NOT NULL auto_increment,
  `voce` char(20) NOT NULL,
  `prezzo` decimal(5,2) NOT NULL,
  PRIMARY KEY  (`codice`)
) ENGINE=InnoDB;

In questa tabella usiamo un id autoincrementante (piccolo perchè i valori
saranno pochi), un campo per descrivere la voce e uno per il prezzo; da
notare che questo campo potrà avere valori negativi, in quanto in alcuni
casi (ad esempio il trattamento di soggiorno e prima colazione) non si
tratta di supplementi ma di riduzioni.

Passiamo ora alle prenotazioni, che memorizzeremo nella
seguente tabella:

CREATE TABLE `prenotazioni` (
  `id` mediumint unsigned NOT NULL auto_increment,
  `periodoDal` date NOT NULL,
  `periodoAl` date NOT NULL,
  `camera` smallint unsigned NOT NULL,
  `idCliente` mediumint unsigned NOT NULL,
  `prezzoTotale` decimal(7,2) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `camera` (`camera`),
  KEY `idCliente` (`idCliente`)
) ENGINE=InnoDB;

Anche in questo caso usiamo un identificativo autoincrementante, seguito
dalle date di inizio e fine soggiorno, dai riferimenti a camera e cliente
(che sono chiavi esterne sulle rispettive tabelle), e dal prezzo totale
della prenotazione; per favorire i collegamenti con le tabelle dei
clienti e delle camere abbiamo indicizzato anche i campi relativi.

L'ultima tabella del nostro database ci serve per memorizzare i
supplementi relativi a ciascuna prenotazione. Si tratta
di una relazione 1:N, nel senso che ad ogni prenotazione possono
corrispondere più supplementi (o riduzioni): ad esempio la culla, il
terzo letto, la pensione completa ecc.; questo è il motivo per cui è
necessaria una tabella distinta da quella delle prenotazioni, in base
alle regole della normalizzazione.

CREATE TABLE `supplementi_prenotati` (
  `idPrenotazione` mediumint unsigned NOT NULL,
  `codiceSupplemento` tinyint unsigned NOT NULL
) ENGINE=InnoDB;

Come vedete qui non abbiamo altro che i riferimenti alla tabella delle
prenotazioni e a quella dei supplementi.

Provvediamo ora, prima di passare agli esempi sulle query, a riempire le
tabelle relative alle camere e ai prezzi:

INSERT INTO `camere` VALUES
(101, 1, 'singola', '', 'ariaCondizionata'),
(102, 1, 'doppia', '', 'ariaCondizionata,vistaMare,tv'),
(103, 1, 'doppia', 'terzo', 'ariaCondizionata,vistaMare,tv'),
(104, 1, 'matrimoniale', 'terzo,quarto', 'ariaCondizionata'),
(201, 2, 'matrimoniale', 'terzo', 'ariaCondizionata,vistaMare,tv'),
(202, 2, 'matrimoniale', 'terzo', 'ariaCondizionata,vistaMare,tv'),
(203, 2, 'matrimoniale', 'terzo,quarto', 'ariaCondizionata'),
(301, 3, 'matrimoniale', 'terzo', 'ariaCondizionata,vistaMare,tv'),
(302, 3, 'matrimoniale', 'terzo', 'ariaCondizionata,vistaMare,tv'),
(303, 3, 'matrimoniale', 'terzo,quarto', 'ariaCondizionata'),
(401, 4, 'singola', '', 'fumatori'),
(402, 4, 'tripla', 'quarto', 'fumatori,vistaMare'),
(403, 4, 'tripla', 'quarto', 'fumatori');

INSERT INTO `prezzi` VALUES
('2006-05-01', '2006-05-31', 'singola', '50.00'),
('2006-05-01', '2006-05-31', 'doppia', '90.00'),
('2006-05-01', '2006-05-31', 'matrimoniale', '90.00'),
('2006-05-01', '2006-05-31', 'tripla', '130.00'),
('2006-06-01', '2006-06-30', 'singola', '55.00'),
('2006-06-01', '2006-06-30', 'doppia', '95.00'),
('2006-06-01', '2006-06-30', 'matrimoniale', '95.00'),
('2006-06-01', '2006-06-30', 'tripla', '140.00'),
('2006-07-01', '2006-07-31', 'singola', '65.00'),
('2006-07-01', '2006-07-31', 'doppia', '120.00'),
('2006-07-01', '2006-07-31', 'matrimoniale', '120.00'),
('2006-07-01', '2006-07-31', 'tripla', '160.00'),
('2006-08-01', '2006-08-31', 'singola', '80.00'),
('2006-08-01', '2006-08-31', 'doppia', '150.00'),
('2006-08-01', '2006-08-31', 'matrimoniale', '150.00'),
('2006-08-01', '2006-08-31', 'tripla', '200.00'),
('2006-09-01', '2006-09-30', 'singola', '50.00'),
('2006-09-01', '2006-09-30', 'doppia', '90.00'),
('2006-09-01', '2006-09-30', 'matrimoniale', '90.00'),
('2006-09-01', '2006-09-30', 'tripla', '130.00');

INSERT INTO `supplementi` VALUES
(1, 'culla', '8.00'),
(2, 'letto aggiuntivo', '30.00'),
(3, 'uso singola', '-15.00'),
(4, 'bed&breakfast', '-10.00'),
(5, 'pensione completa', '5.00');

Ora possiamo passare all'utilizzo del nostro database!