CREATE TABLE SpecialityDoctors(
id int not null primary key,
name varchar(256),
);
CREATE TABLE Doctors(
id int not null primary key,
fullName varchar(256),
specialityId int not null
CONSTRAINT FK_Doctors_SpecialityDoctors FOREIGN KEY (specialityId)
REFERENCES SpecialityDoctors(id)
);
CREATE TABLE Patients(
id int not null primary key,
fullName varchar(256)
);
CREATE TABLE Preferences(
id int not null primary key,
IdSpeciality int not null,
IdPatient int not null,
Preference varchar(512)
CONSTRAINT FK_Preference_SpecialityDoctors FOREIGN KEY (IdSpeciality)
REFERENCES SpecialityDoctors(id)
,
CONSTRAINT FK_Preference_Patients FOREIGN KEY (IdPatient)
REFERENCES Patients(id)
);
CREATE TABLE Appointment(
id int not null primary key,
IdDoctor int not null,
IdPatient int not null,
apTime datetime not null,
CONSTRAINT FK_Appointment_Doctors FOREIGN KEY (IdDoctor)
REFERENCES Doctors(id)
,
CONSTRAINT FK_Appointment_Patients FOREIGN KEY (IdPatient)
REFERENCES Patients(id)
);
INSERT INTO SpecialityDoctors (id, name)
VALUES
(1, 'Cardiologist'),
(2, 'Dermatologist'),
(3, 'Endocrinologist');
-- -- INSERT INTO Doctors
-- INSERT INTO Doctors (id, fullName, specialityId)
-- VALUES
-- (1, 'John Smith', 1),
-- (2, 'Anna Johnson', 2),
-- (3, 'Michael Davis', 3);
-- -- INSERT INTO Patients
-- INSERT INTO Patients (id, fullName)
-- VALUES
-- (1, 'Emily Brown'),
-- (2, 'Daniel Wilson'),
-- (3, 'Sophia Martinez');
-- -- INSERT INTO Preference
-- INSERT INTO Preferences (Id, IdSpeciality, IdPatient, Preference)
-- VALUES
-- (1, 1, 1, 'Afraid of injections '),
-- (2, 2, 2, 'I have soft skin,'),
-- (3, 3, 3, 'I will only go to a woman');
-- -- INSERT INTO Appointment
-- INSERT INTO Appointment (Id, IdDoctor, IdPatient, "Time")
-- VALUES
-- (1, 1, 1, '2023-05-17 10:00:00'),
-- (2, 2, 2, '2023-05-18 14:30:00'),
-- (3, 3, 3, '2023-05-19 09:45:00');
SELECT
p.fullName AS PatientName,
d.fullName AS DoctorName,
s.name AS Specialty,
a.apTime AS AppointmentTime,
pr.Preference as Preference
FROM
Appointment a
JOIN
Doctors d ON d.id = a.IdDoctor
JOIN
Patients p ON p.id = a.IdPatient
JOIN
Preferences pr ON pr.IdPatient = p.id AND pr.IdSpeciality = d.specialityId
JOIN
SpecialityDoctors s ON s.id = pr.IdSpeciality;
<?php
class Doctor {
public $id;
public $fullName;
public $specialityId;
public function __construct($id, $fullName, $specialityId){
$this-> id = $id;
$this-> fullName = $fullName;
$this-> specialityId = $specialityId;
}
};
class Patient {
public $id;
public $fullName;
public function __construct($id,$fullName){
$this->id = $id;
$this->fullName = $fullName;
}
}
class Preferences {
public $id;
public $idSpeciality;
public $idPatient;
public $preference;
public function __construct($id,$idSpeciality, $idPatient,$preference){
$this->id = $id;
$this->idSpeciality = $idSpeciality;
$this->idPatient = $idPatient;
$this->preference = $preference;
}
}
class Appointment{
public $id;
public $idDoctor;
public $idPatient;
public $time;
public function __construct($id, $idDoctor, $idPatient, $time){
$this -> id = $id;
$this -> idDoctor = $idDoctor;
$this -> idPatient = $idPatient;
$this -> time = $time;
}
}
function findById($arr,$id){
foreach( $arr as $el){
if($el->id == $id){
return $el;
}
}
return null;
}
$doctors = [
new Doctor(1, 'John Smith', 1),
new Doctor(2, 'Anna Johnson', 2),
new Doctor(3, 'Michael Davis', 3)
];
$patients = [
new Patient(1, 'Emily Brown'),
new Patient(2, 'Daniel Wilson'),
new Patient(3, 'Sophia Martinez')
];
$preferences = [
new Preferences(1, 1, 1, 'Afraid of injections '),
new Preferences(2, 2, 2, 'I have soft skin,'),
new Preferences(3, 3, 3, 'I will only go to a woman')
];
$appointments = [
new Appointment(1, 1, 1, '2023-05-17 10:00:00'),
new Appointment(2, 2, 2, '2023-05-18 14:30:00'),
new Appointment(3, 3, 3, '2023-05-19 09:45:00')
];
// print_r($doctors);
// print_r($patients);
// print_r($preferences);
// print_r($appointments);
foreach($appointments as $appointment ){
$doctor = findById($doctors,$appointment->idDoctor);
$patient = findById($patients, $appointment->idPatient);
$preference = findById($preferences,$patient->id);
$db::table('Patients')->insert(['id'=> $patient->id,'fullName'=>$patient->fullName]);
$db::table('Doctors')->insert(['id'=> $doctor->id,'fullName'=>$doctor->fullName, 'specialityId'=>$doctor->specialityId]);
$db::table('Preferences')->insert(['id'=> $preference->id,'IdSpeciality'=>$preference->idSpeciality, 'IdPatient'=>$preference->idPatient,'Preference'=>$preference->preference]);
$db::table('Appointment')->insert(['id'=> $appointment->id,'IdDoctor'=>$appointment->idDoctor, 'IdPatient'=>$appointment->idPatient,'apTime'=>$appointment->time]);
// print_r($appointment);
// print_r($doctor);
// print_r($patient);
// print_r($preference);
}
$doctorsDB = $db::table('Doctors')->get();
print_r($doctorsDB);