CREATE TABLE `tbl_Appointments`(
`Appiontment_Id` INT AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Appiontment_Id),
`Patient_name` varchar(100) NOT NULL,
`Patient_cnic` varchar(100) NOT NULL,
`Patient_gender` varchar(100) NOT NULL,
`Patient_contact` varchar(100) NOT NULL,
`Patient_email` varchar(100) NOT NULL,
`Department_id` int NOT NULL,
`Doctor_id` int NOT NULL,
`Datetime_id` int NOT NULL,
`Patient_message` text NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL
)
;
CREATE TABLE `tbl_Records_Doctors`(
`Doctor_id` int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Doctor_id),
`Doctor_name` varchar(200) NOT NULL,
`Designation_id` int NOT NULL,
`Qualification_id` int NOT NULL,
`Department_Id` int NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL,
FOREIGN KEY (Designation_id) REFERENCES tbl_designation(Designation_id),
FOREIGN KEY (Qualification_id) REFERENCES tbl_qualification(Qualification_id),
FOREIGN KEY (Department_Id) REFERENCES tbl_departments(Department_Id)
)
;
CREATE TABLE `tbl_Designation`(
`Designation_id` int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Designation_id),
`Designation_name` varchar(200) NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL
)
;
CREATE TABLE `tbl_Qualification`(
`Qualification_id` int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Qualification_id),
`Qualification_Disc` varchar(200) NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL
)
;
CREATE TABLE `tbl_Departments`(
`Department_Id` int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Department_Id),
`Department_name` varchar(200) NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL
)
;
CREATE TABLE `tbl_Datetime`(
`Datetime_id` int AUTO_INCREMENT NOT NULL,
PRIMARY KEY (Datetime_id),
`Datetime_Disc` varchar(200) NOT NULL,
`Doctor_id` int NOT NULL,
`Created_On` datetime DEFAULT CURRENT_TIMESTAMP NOT NULL,
`Date_On` date DEFAULT CURRENT_DATE NOT NULL,
FOREIGN KEY (Doctor_id) REFERENCES tbl_records_doctors(Doctor_id)
)
;
CREATE VIEW tbl_view_doctors AS
SELECT tbl_records_doctors.Doctor_id,tbl_records_doctors.Doctor_name,tbl_qualification.Qualification_Disc,tbl_designation.Designation_name,tbl_departments.Department_name FROM tbl_records_doctors
INNER JOIN tbl_qualification ON tbl_qualification.Qualification_id=tbl_records_doctors.Qualification_id
INNER JOIN tbl_designation ON tbl_designation.Designation_id=tbl_records_doctors.Designation_id
INNER JOIN tbl_departments ON tbl_departments.Department_Id=tbl_records_doctors.Department_Id
SELECT tbl_appointments.Appiontment_Id,tbl_appointments.Patient_name,tbl_appointments.Patient_cnic,tbl_appointments.Patient_gender,tbl_appointments.Patient_contact,tbl_appointments.Patient_email,tbl_appointments.Patient_message,tbl_departments.Department_name,tbl_records_doctors.Doctor_name,tbl_datetime.Datetime_Disc FROM tbl_appointments
INNER JOIN tbl_departments ON tbl_departments.Department_Id=tbl_appointments.Department_id
INNER JOIN tbl_records_doctors ON tbl_records_doctors.Doctor_id=tbl_appointments.Doctor_id
INNER JOIN tbl_datetime ON tbl_datetime.Datetime_id=tbl_appointments.Datetime_id
