具有多种条件的MySQL预留系统

Question

I'm writing a PHP/MySQL application for tracking appointments and I'm stuck on locating the next available appointment based on whether any number of conditions are selected.

CREATE TABLE IF NOT EXISTS `appointments` (
`appointmentID` int(9) unsigned NOT NULL AUTO_INCREMENT,
`patientID` int(9) unsigned NOT NULL,
`apptTitle` varchar(50) NOT NULL,
`apptDate` date NOT NULL,
`apptTime` time NOT NULL,
`apptLength` int(4) NOT NULL,
`apptStatus` varchar(25) NOT NULL,
`physician` int(9) unsigned NOT NULL,
`apptType` varchar(30) NOT NULL,
`apptInvoice` varchar(10) NOT NULL,
`apptNotes` varchar(1000) NOT NULL,
`apptLocation` varchar(25) NOT NULL,
`apptReminder` int(4) NOT NULL,
 PRIMARY KEY (`appointmentID`),
 KEY `patientID` (`patientID`),
 KEY `physician` (`physician`),
 KEY `apptStatus` (`apptStatus`),
 KEY `apptLocation` (`apptLocation`),
 KEY `apptType` (`apptType`)
 ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

Side note: You'll notice my Type, Location, Status fields are varchar's, and they're linked to lookup tables. So, I'm storing the actual value, not an ID for each item (so each lookup table is just the field value as the PK, no ID column). I've done this to reduce joins in the future, but I know this is slightly denormalized. If this is the wrong way to go, feel free to let me know, I just hadn't found overwhelming evidence one way or the other besides knowing I wouldn't have to do those joins later if I denormalized a bit. There is "on update" logic in place so if a change is made it will update the values in the appointments table.

The goal here is to show all available appointment slots (these are 15-minute slots, so 12:00am-12:14:59am, 12:15:00am-12:29:59am, etc.). By default, I'd just show all appointment slots for the next X days (probably 14) with no restrictions. But, if a patient wants to see a particular doctor, I'd want to limit to that, or if a doctor only needs a consultation, I can limit the location to a consultation room and not take up an exam room. Or, we know a follow-up needs to be scheduled for X months, so I can start showing days after that time has elapsed. Or, patient can only make appointments on weekends or after 5pm.

From what I've read, I need some sort of appointment "slots" table, but I'm not sure how I would structure that table so I could compare it against the existing appointments and find my slots.

I know a decent bit about MySQL and I'm always up for a challenge, but I can't seem to wrap my head around how I would do this. I've looked for similar questions, but none really seemed to cover the amount of customization I'm looking for and I couldn't figure out how to tweak that code to make it work for me. Hopefully, someone can help me understand where I need to go with this idea!

Thank you in advance for your help!

Answer 1

I don't think planning for future "reduction of joins" is a good reason. You should be more worried about the future headache you're creating (for you or someone else) with the "update logic". Like you said, fields such as type, location, and status could have metadata relevant to your slots query (type "requires followup in X months", location "is exam room"), so you're going to need to join eventually anyway. Even just to find available locations for a time slot, you'll definitely need to join, if I understand correctly.

I'm guessing a "slot" is like a 5 minute timespan? Eg. 12:00-12:04:59, 12:05-12:09:59, etc?

Like Alex said above, you'll need an "all possible slots" table to left join against (temporary or not).

I'd probably create it permanently on disk, since it's referenced a lot. It sounds like a waste, but the other option is to generate it dynamically each time, which isn't great either.

Then to say find a doctors available slots, something like:

SELECT a.* 
FROM slots AS a
LEFT JOIN appointments AS b
ON a.startDate BETWEEN b.apptDate AND DATE_ADD(b.apptDate,INTERVAL b.apptTime MINUTE)
AND b.physician=1234 
WHERE b.appointment IS NULL

I can try to help with your other more complicated queries if this sounds like it's on the right track of what you're after.