samedi 9 mai 2015

Appointment Timeslots in SQL

I have a clinic table which contains:

CREATE TABLE tClinic
(   clinic_id INTEGER IDENTITY(1,1) NOT NULL,
    clinic_name CHAR(30) NOT NULL,
    CONSTRAINT tClinic1 PRIMARY KEY(clinic_id)
);

Doctor table

CREATE TABLE tDoctor
(   doctor_ID INTEGER IDENTITY(1,1) NOT NULL, 
    staff_id INTEGER NOT NULL,
    specialisation CHAR(40) NOT NULL,  
    clinic_id INTEGER NOT NULL, 
  available_time TIME NOT NULL, 
    password CHAR(7) NOT NULL,
    CONSTRAINT tDoctor1 PRIMARY KEY(doctor_ID),
    CONSTRAINT tStaff4 FOREIGN KEY(staff_id) REFERENCES tStaff(staff_id),
    CONSTRAINT tClinic2 FOREIGN KEY(clinic_id) REFERENCES tClinic(clinic_id)
);

Appointment Table

CREATE TABLE tAppointments 
(   appointment_id INTEGER IDENTITY(1,1) NOT NULL, 
    doctor_ID INTEGER NOT NULL,
    patient_id INTEGER NOT NULL,
    date_of_appointemnt DATE NOT NULL,
    time_of_appointment TIME NOT NULL, 
    CONSTRAINT tAppointments1 PRIMARY KEY(appointment_id),
    CONSTRAINT tDoctor2 FOREIGN KEY(doctor_ID) REFERENCES tDoctor(doctor_ID),
    CONSTRAINT tPatient2 FOREIGN KEY(patient_id) REFERENCES tPatient(patient_id)
);

Now I am unsure of how to implement so that each time slot of appointment is be default 30 minutes. I need it display dates and time slots for each of the doctors working at the clinic...

Aucun commentaire:

Enregistrer un commentaire