What is the best way to set this up?

[mellis95]

I may be in over my head hear, but I would like to start development on an open source medical scheduler application. The basic idea is a main schedule grid with physician names across the top and times, in 15 minute increments down the left side. It must support multiple physicians. I have been thinking about how to layout the tables off and on for a few weeks now and I could use some advice as to whether or not I am heading in the right direction. I am thinking about this, but I am really not too confident in what I have so far:

 

tbl_physician:: physician_id, last_name, first_name, other physician information, etc...

tbl_schedule:: appointment_id, date, physician_id, patient_id, time

tbl_patient:: patient_id, last_name, first_name, other patient information, etc.....

 

Then it would all come together in something like this:

 

SELECT a.appointment_id, b.physician_id, c.patient_id, a.time 
FROM tbl_appointment AS a
LEFT JOIN tbl_physician AS b
ON b.physician_id=c.physician_id
LEFT JOIN tbl_patient AS c 
ON c.patient_id=a.appointment_id
WHERE date=$date_selected_by_user

 

and I would use the a.time field to determine where the data falls on the grid for the relevant physician..

 

I have never used AJAX before, but I think that would be the best way to present/modify the data in the schedule grid.

 

Does this sound like I am headed in the right direction, before I get too deep?

 

Thanks for the help.

[TheFilmGod]

Some recommendations: The appointment_id is nothing more than a surrogate key. It is required because you can possibly have two appointments scheduled at the same time. However, the key you would want to query against is the date/time.

 

Date/time should be combined into a field by mysql's supported "datetime" type.

 

Come to think of it, should you honestly use datetime? Maybe you should use a separate date and separate time (as you orginally had it), but the time should be a decimal(3,1). Each appointment is by 15 minute increments, right? So 3:00, 4:15, 6:30, 8:45 could all be 3.0, 4.1, 6.2, 8.3 respectively. You'd use a 24 hour clock. So 4:00 am would be 16.0. Just some ideas. This type of optimization can be considered "fine-tuning" that it wouldn't be worthy spending 10 or more minutes on.

[roopurt18]

I can't think of any reason to use separate columns for date and time.

 

An appointment can not exist without both a patient and a physician so your query should be INNER JOINs and you should be using foreign key restrictions on the appointments table.

 

I would also consider breaking the appointment times into a start_tm and end_tm.  Appointments may vary in length due to the nature of the appointment.  You can't just assume 8:15AM is available because the only other appointment available that day is 7:45AM.

 

You might also consider the possibility that a facility will have a team of physicians with no particular physician tied to an appointment.  In that case, perhaps your query will be a LEFT JOIN on the physician table.

 

You may also want to provide the facility for physicians to mark slots of time as unavailable as well.  Physicians are people too and have their own appointments, obligations, and vacations to see to.  You wouldn't want a patient's appointment to be scheduled on top of a physician's vacation week.

[mellis95]

Thank you for the suggestions. Using start_time and end_time is a great idea that I will definitely need to make use of.

 

I do have a follow-up question on this:

I can't think of any reason to use separate columns for date and time.

 

I have not used timestamps before, which is I assume what would need to be used to combine date/time into a single column? If not a timestamp, then what?

 

Thanks again for the advice.

[roopurt18]

DATETIME