Dovendo memorizzare gli stessi dati per i pazienti e per il personale dello studio(soprattutto personale medico) utilizziamo un'unica tabella che chiamiamo DatiAnagrafici e due tabelle aggiuntive una per gli Indirizzi e una per i Numeri di telefono.
Vantaggi: riduzione del numero di tabelle
Tabelle Indirizzi e NumTel utilizzate sia per il personale dello studio che per i pazienti(non devo creare tabelle separate).
Se utilizzassimo tabelle distinte per il personale dello studio e per i pazienti dovremmo aggiungere altre 3 tabelle(PersonaleStudio, NumTelPersonale,IndirizziPersonale).
Svantaggi:
1. rallentamento nell'esecuzione di alcune interrogazioni, 2. problema di distinguere il personale dello studio dai pazienti 1.
Ad esempio per “visualizzare l'elenco dei medici”, invece di una decina di record, è necessario esaminarne qualche migliaio(la tabella comprende infatti anche i dati dei pazienti).
Tuttavia, per i numeri considerati, la differenza nei tempi non sarebbe percepibile dall'utente e volendo si potrebbe utilizzare una tabella ausiliaria Medici che contenesse gli id dei Medici(nella tabella DatiAnagrafici) o semplicemente si potrebbero memorizzare i risultati dell'interrogazione sul server (ovvero il resultset) evitando di eseguire la query più volte(visto che i componenti dello studio non varieranno molto frequentemente) aggiornando tali risultati ogni volta che si inserisce un nuovo medico.
2.
Potrei andare ad esaminare i titoli o creare una tabella ausiliaria Medici come suggerito prima, ma più semplicemente possiamo utilizzare un attributo “tipo” che indica se i dati anagrafici sono relativi ad un medico, ad un paziente o semplicemente a qualcuno che lavora nello studio(es.
Segretaria)
tipo = 0 : paziente,
= 1 : medico,
= 2 : segretaria,
= 3: fisioterapista, …
INDIRIZZI E NUMERI DI TELEFONO:
Per gli indirizzi e i numeri di telefono si è aggiunto un campo descrizione:
Ad esempio un numero di telefono può essere associato ad una descrizione del tipo “fisso casa”
oppure “Studio Roma” oppure “cellulare”, mentre un indirizzo può avere come descrizione “ex moglie”, “Studio Milano (solo Venerdì e Sabato)” o più normalmente “residenza” , “domicilio”,
“casa del figlio”, ecc...
La soluzione proposta in questo caso è stata scelta perchè presenta aspetti interessanti dal punto di vista didattico oltre che ridurre in maniera sostanziale il numero di tabelle necessarie.
TITOLI:
Titoli: specializzazioni ma non solo(nella traccia si fa riferimento a generici “dati
professionali” ). La specializzazione è quello che interessa di più ma sapere che si è Presidente dell'ordine dei medici o Direttore di un importante Centro Ospedaliero o del Reparto di
CardioChirurgia di un prestigioso ospedale avrebbe sicuramente il suo peso.
E' possibile aggiungere un attributo tipo che specifica il tipo di Titolo(es. 0 : titolo di laurea, 1 : specializzazione, 2 : altro)
Indirizzi, Numeri di Telefono,Titoli sono non a caso al plurale. Per Indirizzi,Numeri di Telefono è necessario creare delle tabelle aggiuntive e collegarle N:1 con DatiAnagrafici. L' indirizzo email si può invece mettere direttamente insieme alle informazioni anagrafiche.
Per quanto riguarda Titoli, l'associazione Titoli-DatiAnagrafici è del tipo N:N in quanto lo stesso titolo può essere stato acquisito da più persone(es . più membri dello studio saranno laureati in Medicina e Chirurgia, idem per infermieri e fisioterapisti ) e una persona può avere più titoli(in effetti un medico ne avrà almeno 2: laurea e specializzazione). Per questo è necessario creare un'altra tabella DatiAnag_Titoli che contiene le chiavi esterne che collegano i dati anagrafici dei possessori ai titoli acquisiti.
VISITE Attributi:
id: chiave primaria
idPaziente, idMedico : chiavi esterne fanno riferimento sempre all'id di DatiAnagrafici data_prenot e ora_prenot (data ora della prenotazione),
descrizione : impostata dal medico come promemoria della diagnosi, delle prescrizioni, ecc...
durata_prevista : impostata dal medico in base all'anamnesi del paziente,
versato : pagamento effettuato dal paziente corrispondente in genere al costo della prestazione o ad una sua parte,
costo : costo della singola prestazione,
ora_inizio,ora_fine : questi attributi non sono esplicitamente richiesti ma potrebbero risultare utili per effettuare una stima del tempo medio effettivamente impiegato per visita o per valutare il costo della visita stessa in base alla sua durata. Inoltre, l'ora di inizio può essere utilizzata per stabilire se questa sia stata effettuata o meno (al posto dell'attributo “effettuata” utilizzato nelle soluzioni del Corriere e Alternativa): se tale valore non è specificato(= NULL) ciò significa che la visita non è stata effettuata.
Considerazioni:
Notare che si è preferito separare la data dall'ora della prenotazione anche se sarebbe stato possibile utilizzare un unico campo datetime questo per rendere più semplici la parte SQL nel momento in cui si deve aggiornare solo la data o solo l'ora.
Riguardo al tempo medio previsto per ogni visita credo che debba essere specificato nella forma dell'attributo durata_prevista nella tabelle Visite e non a priori per medico!
Per quanto riguarda la fatturazione, ovvero il “costo delle singole prestazioni”, l'aggettivo singole mi fa pensare che sarebbe meglio mettere tale valore nella tabella Visite e non creare una tabella a parte anche se avrebbe senso. Questo per semplificare un po' il problema e perchè il paziente
potrebbe versare la quota in varie rate ad ogni visita, per cui mettendo un attributo versato in Visite si potrebbe registrare anche questa informazione(quando è stato pagato ed in che data) e non solo il saldo finale. L'attributo descrizione aiuterebbe a classificare le varie visite (di controllo) non pagate e l'attributo costo permetterebbe di calcolare l'importo dovuto sia che venga contabilizzato solo per l'ultima visita sia che venga spalmato su ogni visita.
ORARIO VISITE:
In merito alla frase “l'orario delle visite”, credo che effettiavamente sia necessario inserire anche le informazioni relative all'orario di ricevimento dei vari medici dello studio...altrimenti prendere appuntamenti potrebbe essere difficile!.
L'orario sarà qualcosa di simile lunedì dalle 8:00 alle 12:00, Martedì dalle 11:00 alle 15:00,ecc...
OrarioVisite : = <id,giorno, ora_inizio, ora_fine, idMedico>
In finale si ottiene:
DatiAnagrafici := < id, nome, cognome, data_nascita,comune_nascita, cod_fiscale, email,tipo >
Titoli := < id, titolo, tipo>
DatiAnagrafici_Titoli := <idAnagrafica , idTitolo>
Indirizzi := < id, via_piazza, num_civico, cap, città, descrizione, idUtente >
NumTel:= < id, tel, descrizione, idUtente>
Visite :=< id, idPaziente, idMedico,data_prenot,ora_prenot,descrizione, durata_prevista, costo, versato, ora_inizio, ora_fine >
OrarioVisite : = <id,giorno, ora_inizio, ora_fine, idMedico>
CREATE TABLE Visite_mediche.DatiAnagrafici (
id int unsigned NOT NULL auto_increment, cognome varchar(30) NOT NULL,
nome varchar(30) NOT NULL, email varchar(30) ,
data_nascita date , comune_nascita varchar(30) , cod_fiscale char(16) ,
tipo int(1) unsigned NOT NULL ,
PRIMARY KEY (id)
KEY nome_cognome (cognome,nome) );
Notare il codice fiscale di 16 caratteri definito come char e non varchar(avendo lunghezza costante).
La chiave primaria è stata impostata come unsigned(un valore senza segno) : non è necessario ma se si specifica ci si deve ricordare di impostare anche la chiave esterna dello stesso tipo.
Nota: si deve mettere unsigned prima di NOT NULL e non dopo altrimenti MySQL rileva un errore di sintassi.
Si è creato un indice su nome e cognome del paziente per accelerare le ricerche sui pazienti che coinvolgano entrambi questi attributi.
CREATE TABLE Visite_mediche.Indirizzi (
id int(10) unsigned NOT NULL auto_increment , via_piazza varchar(45) NOT NULL, num_civico int(6) unsigned NOT NULL ,
cap int(6) unsigned NOT NULL ,
città varchar(45) NOT NULL, descrizione varchar(45) NOT NULL,
idUtente int(10) unsigned NOT NULL ,
PRIMARY KEY (id),
FOREIGN KEY (idUtente) REFERENCES DatiAnagrafici(id) ) ;
CREATE TABLE Visite_mediche.NumTel (
id int(10) unsigned NOT NULL auto_increment ,
tel varchar(15) NOT NULL,
descrizione varchar(45),
idUtente int(10) unsigned NOT NULL ,
PRIMARY KEY (id),
FOREIGN KEY (idUtente) REFERENCES DatiAnagrafici(id) ) ;
CREATE TABLE Visite_mediche.Visite (
id int(10) unsigned NOT NULL auto_increment, data_prenot date NOT NULL,
ora_prenot date NOT NULL, idMedico int(10) unsigned NOT NULL, idPaziente int(10) unsigned NOT NULL, durata_prevista int(3) unsigned default 30 ,
descrizione varchar(200),
costo int(6) unsigned default 0, versato int(6) unsigned default 0,
ora_inizio time DEFAULT NULL,
ora_fine time, PRIMARY KEY (id),
FOREIGN KEY (idMedico) REFERENCES DatiAnagrafici(id), FOREIGN KEY (idPaziente) REFERENCES DatiAnagrafici(id) ) ;
Notare che si è assegnato un valore di default alla durata media prevista per la visita, quindi se non si specifica alcun valore verrà impostato automaticamente a 30 (minuti). Analogamento si è
impostato il valore di default 0 per costo e versato.
Gli attributi costo,versato sono stati considerati interi perchè è improbabile che si paghino anche frazioni di euro.
CREATE TABLE Visite_mediche.Titoli (
id int(10) unsigned NOT NULL auto_increment,
titolo varchar(60) NOT NULL,
tipo int(1) unsigned NOT NULL,
PRIMARY KEY (id) ) ;
CREATE TABLE Visite_mediche.DatiAnag_Titoli ( idUtente int(10) unsigned NOT NULL,
idTitolo int(10) unsigned NOT NULL, PRIMARY KEY(idUtente,idTitolo),
FOREIGN KEY (idUtente) REFERENCES DatiAnagrafici (id), FOREIGN KEY (idTitolo) REFERENCES Titoli(id)
) ;
CREATE TABLE Visite_mediche.Orarivisite(
id int(10) unsigned NOT NULL auto_increment,
giorno varchar(10) NOT NULL,
ora_inizio time NOT NULL,
ora_fine time NOT NULL,
idMedico int(10) unsigned NOT NULL, PRIMARY KEY (id),
FOREIGN KEY (idMedico) REFERENCES DatiAnagrafici(id) ) ;
Nelle seguenti query le espressioni racchiuse tra parentesi quadrate indicano dei valori che
dovrebbero essere inseriti attrvaerso l'interfaccia grafica o testuale al DB(Ad esempio attraverso un form HTML e uno script PHP lato server che si occupi di leggere i valori della richiesta, di
effettuare l'interrogazione al RDBMS MySQL e di visualizzarne i risultati).
1. elenco giornaliero delle visite prenotate per ogni singolo medico
Considerando uno specifico medico e uno specifico giorno:
Se consideriamo il termine “giornaliero” come sinonimo di “per la data odierna”:
2. elenco giornaliero visite prenotate e non effettuate
(Stesso discorso di prima per quanto riguarda il termine “giornaliero” ) SELECT nome,cognome,ora_prenot, descrizione,durata_prevista FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id) AND (data_prenot = [data visite]) AND (ora_inizio IS NULL)
SELECT nome,cognome,ora_prenot, descrizione,durata_prevista FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id) AND (idMedico = [id medico]) AND (Visite.Data_prenot = [data visite])
SELECT nome,cognome,ora_prenot, descrizione,durata_prevista FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id) AND (idMedico = [id medico]) AND (data_prenot = CURDATE())
3.elenco settimanale con gli appuntamenti di ogni medico suddivisi per giorno e per ora
Se considero uno specifico medico(devo inserire l'id del medico) per ricavare le date di inizio e fine settimana si può utilizzare la funzione:
WEEKDAY(data) ritorna 0 = Lunedì, 1 = Martedì, … 6 = Domenica
( in passato abbiamo visto DAYOFWEEK( data) che ritorna 1 = Domenica, 2 = Lunedì, …, 7 = Sabato
… ovviamente cambiando un po' i calcoli in maniera che la data di inizio e fine corrispondano si possono utilizzare indifferentemente le 2 funzioni. Utilizziamo weekday perchè richiede meno calcoli)
Innanzitutto un esempio
supponiamo che oggi sia martedì → WEEKDAY(CURDATE()) = 1 la data di inizio settimana(ovvero di lunedì) sarebbe la data di ieri ovvero: CURDATE() - INTERVAL 1 DAY
se invece fosse giovedì → WEEKDAY(CURDATE()) = 3 la data di inizio settimana(ovvero di lunedì) sarebbe la data di 3 giorni fa ovvero: CURDATE() - INTERVAL 3 DAY
In generale possiamo trovare la data di inizio settimana nel seguente modo:
curdate() - INTERVAL weekday(curdate()) DAY
Per trovare la data di fine settimana dobbiamo eseguire un piccolo calcolo
Se oggi fosse lunedi la data di fine settimana sarebbe tra 6 giorni, se oggi fosse martedì sarebbe tra 5 giorni e così via...è possibile vedere che in generale utilizando la funzione WEEKDAY() posso trovare tale data in questa maniera
curdate() + INTERVAL 6 - weekday(curdate()) DAY
poiché la data di prenotazione deve ricadere tra data inizio settimana e data fine settimana utilizzando BETWEEN <data_inizio> AND <data_fine> si ottiene
La suddivisione qui si traduce in un ordinamento.
SELECT data_prenot,nome, cognome, ora_prenot, descrizione, durata_prevista FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id) and (idMedico = [id Medico] )
AND (data_prenot BETWEEN curdate() - INTERVAL weekday(curdate()) DAY AND curdate() + INTERVAL (6 - weekday(curdate())) DAY)
ORDER BY data_prenot, ora_prenot
4. elenco cronologico visite usufruite da ciascun paziente
oppure invece dell'id possiamo utilizzare il codice fiscale come nella Soluzione Alternativa:
La condizione su ora_inizio serve ovviamente a discriminare le visite effettuate da quelle prenotate e non effettuate.
APPROFONDIMENTI
Il fatto di utilizzare una sola tabella per medici e pazienti ci permette di mostrare l'utilità degli ALIAS non solo per le colonne ma anche per le tabelle.
Se volessi visualizzare le visite effettuate mostrando nome e cognome del medico e del paziente al posto delle sole chiavi esterne dovrei scrivere una cosa del genere:
Spiegazione:
ho utilizzato due volte la tabella datiAnagrafici una volta rinominandola medici e un'altra pazienti, poi ho effettuato la solita giunzione tra visite e le 2 tabelle appena citate considerando solo le visite realmente effettuate(ora_inizio IS NOT NULL).
Ho utilizzato degli ALIAS anche per i nomi delle colonne per distinguere il nome e cognome del medico da quello del paziente.
Se si esegue questa query sul database VisiteMediche (che può essere facilmente ricreato grazie allo script SQL) osservando le chiavi esterne e i nomi dei medici e dei pazienti si può verificare che la query effettivamente produce i risultati richiesti.
SELECT data_prenot,nome, cognome, descrizione, ora_inizio,ora_fine FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id)
and (idPaziente = [id Paziente]) and (ora_inizio IS NOT NULL)
SELECT data_prenot,nome, cognome, descrizione, ora_inizio,ora_fine FROM Visite,DatiAnagrafici
WHERE (Visite.idPaziente = DatiAnagrafici.id)
and (cod_fiscale = [cod. fiscale del Paziente]) and (ora_inizio IS NOT NULL)
SELECT medici.nome as nome_medico,medici.cognome as cognome_medico,visite.*,pazienti.nome as nome_paziente, pazienti.cognome as cognome_paziente
FROM datianagrafici as medici,visite,datianagrafici as pazienti
WHERE (idpaziente = pazienti.id) and (idMedico = medici.id) and (ora_inizio IS NOT NULL)
