Hi,

which one is better, having a simple database but you play around deeply in the code or otherwise?
i'm doing an appoitnment system for a clinic right now, and i find some problems on matching the time appointed by the patient with the doctor's schedule.
1 patient can have many appointment and 1 appointment can only be done by specific patient. each appointment duration is about 15 minutes. thus, for instance, a patient books for dr.A which has a schedule on MOnday from 7pm-9pm. she books for 7.45pm by sms and waiting for the confirmation reply. the receptionist will check wether at 7.30 the doctor has got another appointment or not.

my existing design is as the following:
i have 1 table for patient with patientId
appointment with appointmentId, doctorId, staffId, date, fromTime
schedule with scheduleId, dayId, defaultInterval, isAvailable, time
doctor with doctorId
doctor_schedule with doctorId, scheduleId

is this correct or is there any other best solution? because i'm still learning =)
i'm quite confuse with the schedule table, previously i have timeId attribute.

thx for your attention

Well, you have three entities that I see - the Doctor, the Patient, and the exam room (location.) That's three tables, minimum, to support them. Next, you have your schedule, which, as it needs to tie together the three entities, as well as assign a time block to these three entities.

If you don't mind a little waste, (and associated loss of flexibility) by assuming that all appointents are in multiples of 15 minutes, your appointements table could be a three-table intersection of Doctor, Location, and Patient, and have 1 record for each timeslot. (These timeslots could be numbered 1-96; the timeslot divided by 4 yields the time.) This way, the PK for the appointments table would be all three of the foreign entity keys, AND the timeslot. That way, the database itself will raise an error if you try to double-book, and you can also set up unique constraints so that a given location is not booked by multiple doctors (or used by multiple patients) and that doctors won't be overbooked, and that patients can't be in two locations simultaneously.

Hi,

thank's for your reply..
i still get a little confuse with your answer.. =)
so for example,
dr A with speciality A1 works on Monday 7.00-9.00
dr B with speciality A1 works on Tuesday 8.00-12.00
dr C with speciality A2 works on Monday 7.00-10.00,
assume that 00.00 will have the timeslot range from 1-4, then the timeslot for dr A will be 28-36. am i correct?
so i will have another table named TimeSlot that consists of timeslotId and its clockTime (which is 28=7.00, 29=7.15)
and for assuring that the doctor has already got an appointment for that time , i put isAvailable in the schedule table, like i did. Am i correct?

Thank's for your help =P

A timeslot lookup table would probably be simplest.

You could handle conversion in the front end, but this requires "knowlege" of the underlying tables.

You could also write a SQL function (assuming that the database supports user defined conversion functions - a few don't) where you pass time, and it returns timeslot. and a converse function which accepts timeslot and it returns time.

Or, you could create stored procedures to insert the records, and the calling app supplies the DoctorID, PatientID, LocationID, Start time and End time (or duration).


An IsAvaliable field isn't needed. The table structure I discussed above doesn't just state when a doctor is present - it defines when a doctor is present, with a patient, in a location, for a given time slot. Since the primary key of the appointmens table includes all these fields, you cannot enter an appointment which overlaps. You just begin a transaction, and attempt to insert the records. If no error was raised, commit the transaction, else roll back the transaction & notify the user.

And, for Doctor A - the timeslots would be 28 - 35 ... (since each timeslot defines a start time, and is 15 minutes in duration.)