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
Written by Ghouri Admin
We are Creative Blogger Theme Wavers which provides user friendly, effective and easy to use themes. Each support has free and providing HD support screen casting.
0 comments:
Post a Comment