The home page of

Ben Mason
Blog About Projects Photography Countdown

Appointment scheduling in MySQL

29 Oct 2012 18:30

Recently I had to write an appointment scheduling application.

The problem I was faced with was that appointments would generally be recurring (though not always), but the recurrence pattern would be something like the 2nd Monday of every month or the last Friday of each month.

How do you represent something like that in a database?

The solution I can up with was to store the appointments in a table like the one below, with each part of the date as a separate number. Obviously yeardid contains the year e.g. 2012, monthid contains the month (1-12), dayid contains either the day of the month or the day of the week (depending of the value of dayno). If dayno is 0, dayid contains the day of the month (it turned out that 0 was a bad choice due to the automatic type conversion in PHP which was used for the frontend, -1 would have been a better choice for this), if dayno is greater than 0, dayid contains the day of the week (1-7) and dayno represents the nth week day in question in the month (1-5), e.g. dayno 2 and dayid 3 represents the 2nd Tuesday. dayno 5 represents the last occurrence of the week day in question in the month (the 4th or 5th occurrence or a particular week day in a given month depending on which month it is).

CREATE TABLE `tbl_visit` (
`visit_pk` int(11) NOT NULL AUTO_INCREMENT,
`client_fk` int(11) DEFAULT NULL,
`minuteid` int(11) DEFAULT NULL,
`hourid` int(11) DEFAULT NULL,
`dayid` int(11) DEFAULT NULL,
`monthid` int(11) DEFAULT NULL,
`yearid` int(11) DEFAULT NULL,
`dayno` int(11) DEFAULT NULL,
PRIMARY KEY (`visit_pk`),
KEY `FK_visit_enquiry` (`client_fk`)

) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8$$

A stored procedure used to generate a daily diary of appointments to be e-mailed by the scheduling application could use the following in a WHERE clause to select out visit records from tbl_visit that should occur today:

(
(tbl_visit.dayno = 5 AND DATE_ADD(NOW(), INTERVAL 7 DAY) > LAST_DAY(NOW())) -- is today the last of occurrence of whichever weekday it is in the month?
OR
tbl_visit.dayno = CEILING(DAYOFMONTH(NOW()) / 7) -- is today the nth occurrence or whichever day it is in the month?
OR
tbl_visit.dayno IS NULL -- does the appointment happen on every occurrence of a given weekday?
)
AND tbl_visit.dayid = DAYOFWEEK(NOW()) -- is today the right day of the week?

The checks on the other part of the date are a lot simpler i.e. does the value in tbl_visit match today or is it null?

AND
(
tbl_visit.monthid = MONTH(var_date)
OR
tbl_visit.monthid IS NULL

)
AND
(
tbl_visit.yearid = YEAR(var_date)
OR
tbl_visit.yearid IS NULL

)

I appologise for the formatting of the SQL.



Comment on this


VillaDesk 3.5 Released!

18 Jun 2011 16:33

Version 3.5 of the VillaDesk holiday home booking and enquiry management system is now available. If you are an existing user of the software, you can update to the new version by running the update checker from within the software, otherwise, you can download the software from http://www.villadesk.com/ The new version adds support for multiple currencies .i.e. it is now possible to enter payments in the original currency and the system will automatically calulate the payment amount in your selected base currency, based on the exchange rates set up in the software.



4 Comments | Comment on this


Gadgets and things

20 Mar 2011 19:40

486

If 10 years ago someone had told me that in 2011 pretty much everybody would be walking around with what is in essence a powerful, pocket sized, touch-screen computer with reasonably fast Internet access and the added ability to make and receive telephone calls, I would have laughed. Then again, back then I had a Nokia 5110 with a 5 line monochrome display IIRC.

The Internet on a mobile phone, why on earth would anyone want that? Back then, I did actually connect my laptop to my mobile with a data cable and go on the Internet on occasion for various reasons, but I really didn't get why anybody would want mobile websites or even to be able to send and receive e-mail on the move. No, I really didn't.

I must admit that despite being an IT professional, I am something of a luddite when it comes to new technology. I always have been. I don't have all the latest gadgets, not only because I'm poor and can't afford them, but because I rarely acquire a new device unless I see a real benefit for me. When I do buy something, I usually end up hanging on to it until it stops working completely and spare parts can no longer be obtained.

When it comes to software, I can be a little less conservative at times. Sometimes it can be fun and interesting to use new technologies, but in general my attitude is still, if it aint broke, don't fix it.



Comment on this


TinyMCE

24 Nov 2010 21:50

I am trying out the TinyMCE WYSIWYG HTML editor on the part of the site where I add posts. It seems OK, I might use it. This is what it looks like out of the box on the textarea where I enter posts:



2 Comments | Comment on this


Countdown Solver

20 Nov 2010 20:06

The Countdown solver can now produce solutions to the numbers game. Hopefully people will find it useful.

3 Comments | Comment on this


Welcome to my newly renovated website!

17 Nov 2010 21:52

As you can see I have finally gotten round to redesigning my website. Since I don't do Twitter or Facebook, I have added a blog section here that I will update when and if I think of anything to write about. My old site did have a blog than ran off Livejournal, but it wasn't very easy to update and it never worked too well. I have also updated the gallery/photo blog so that I can upload pictures remotely. In hindsight that actually sounds like a somewhat fundemental feature for a photo blog but it was missing from my old photo blog. The photo blog also no longer stores photo captions in an .ini file...

Comment on this

About

Ben Mason is a computer programmer from Reading in England. This is his personal website and blog.

Read More...


Posts by Month

September(0)
August(0)
July(0)
June(0)
May(0)
April(0)
March(0)
February(0)
January(0)
December(0)
November(0)
October(0)

Posts by Category

MySQL(1)
No Category(4)
Programming(1)
Copyright © MM-MMX, Ben Mason