Hi,

i have several question regarding database design.
first, which one is better, having a simple database but you play around deeply in the code or otherwise?
second, 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

Originally Posted by moccacinoe

first, which one is better, having a simple database but you play around deeply in the code or otherwise?

Well if those are the choices, I guess I would have to go with otherwise. I really don't understand your question.

If you have a table of appointments, something like this:

• Doc A: 9:00 am - 9:15 am
• Doc B: 9:00 am - 9:30 am
• Doc C: 9:00 am - 10:00 am
• Doc A: 9:15 am - 9:30 am
• Doc B: 9:30 am - 10:00 am
• and so on

Then you can run a query, depending on your needs. And your needs, well, I don't understand. Does a person call in and want to see their doctor at a particular time? Do they just want an appointment today? Any doctor at 11? What exactly are you trying to do?

But if you wanted to see the answer to any of those questions, you could figure it out by running a query to test whether a doctor is available at a particular time. You would do this by playing with the SQL WHERE clause.

We can see Doc C is booked for a full hour, from 9 to 10, so if someone wanted an appointment at 9:30, this doc is busy. You could find available appointments starting at 9:30 with something like this:

Select * From Schedule Where StartTime <= 9:30 and EndTime >= 9:30

That gets you doctors who are NOT available, so you cross-reference this with your doctors who are working today query, and bada bing, you have your answer.

Hi,

thx for your reply..
sorry if my post is quite confusing.. =)
so, here.. i would like to build an appointment system which could be perfomed by phone, sms or on suggestion by the particular doctor. each appointment has a default duration of 15 minutes, however it can't perfectly happened in the real case.
for instance,
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
A patient wants to book dr A on 7.30, and the receptionist then will check whether dr A at that particular time is busy or not.
if yes, then he will directed to another available timeslot.
based on that, does my proposed database design on previous post already enough? is it quite efficient or not? or do you have another solution...

Many-many thankss...