Appointment system?

[davej]

I've been thinking about the workings of an appointment scheduling system for a class I'm taking where we are not actually doing any programming but just toying with concepts (Systems Analysis) but I'm now kind of confused about how I would actually write such a program. I'm not sure how to make the database an efficient mechanism for correlating available time and reserved time so that you could quickly throw a calendar/chart on the screen that would display available appointment openings. I started off thinking that I would have a table of scheduled appointments where each record would have a start time and an end time and an assigned person. Then I would have a table where each person has their normal availability schedule entered for a typical week. Then maybe I would need an availability_exceptions table where planned holidays, vacations, and out-of-office periods are entered. This all adds up to a fair sized mess that I would not want to process every time someone wants to see when a new appointment could be scheduled, so then I start thinking of other tables where some sort of processed data might be kept, such as perhaps a table containing large boolean arrays of minutes or something. It gets rather messy. Any suggestions? Thanks.

[justsomeguy]

It would make sense to only have one table for events, regardless of whether they are a holiday or appointment. I don't see why you would need more than one table for that. I've had systems that have a lot of data to calculate, where it made sense to use caching. That really only works if the data doesn't get updated all that often though, or at least if the reads substantially outnumber the writes. One example is a timesheet system, where there is a dropdown that lists the available jobs. The database has a table for customers and another one for customer jobs, and it was taking a noticeable amount of time to generate that dropdown where the code gets all active customers, and loops through them and gets each customer's active jobs. The jobs could also be nested to any level, where a job might have sub-jobs with sub-jobs, etc. For hundreds of customers and several jobs per customer, that turned into several seconds. So I set it up so that when a new customer or job was added, or the active status was changed, it would run the code then and generate the option list for the dropdown. Then when someone pulled up the timesheet it only needed to get that option list instead of dynamically calculate the list. So the admins adding customers and jobs have a little extra to wait, but it removes that wait for the rest of the users. That has to do with time though not database complexity. There are plenty of situations where a complex database structure can be fast. If you find that it takes too long to display the calendar then caching that data could help.