DB design - appointment calendar

Rodders

I am building an application which will track my appointments in a calendar style format. I want to be able to create entries starting at 15 minute intervals and each appointment will last between 1 hour and 5 hours. Appointments cannot overlap.

I am scratching my head over the database design.

Should I use a START_TIME and END_TIME in the table? Then use code or SQL to make sure that new entries don't overlap current ones.

Or should I use a START_TIME and DURATION? Again I would use code or SQL to validate the entries.

Also, can anyone suggest a way to show the calendar graphically? What I mean is, I want each 15 minute block to be equal in size and I will use code to change the colours and link href depending on whether an appointment exists or not.

Many Thanks

Rich

tomhath

I'd use START_TIME/END_TIME with the datatype being UNIX Timestamps.

Code vs SQl doesn't matter to me. Purists would say separate the data from the logic, but SQL is code too. Unless you think there's a reasonable chance the database or language could change over the lifetime of the app, do whatever is easiest.

Jack_Slocum

Personally I would use start time with duration and interval. It offers more flexibility. For example, with start time and end time, it is difficult to specify an event which starts at a certain time, but lasts the remainder of the day. Putting in an end time wouldn't truely be accurate since the end time is unknown, but specifying remainder is something that can be done with duration and interval. Also, it's easier to set up reoccuring events with duration/interval.

tomhath

If you are scheduling recurring events, learn how to write and interpret crontab entries. It's a tried and true technique you can extend as needed: http://www.unixgeeks.org/security/newbie/unix/cron-1.html

Rodders

Thanks for all your responses.

My appointments will never be from a given time until the end of the day. I teach people on a one to one basis - can you imagine the stress of being with that same person for 6 hours !?

Not many of my appointments need to repeat. If they do, it is most likely to be for a few weeks only so I can easily code this into my script.

I'm thinking that either option will work suitably. I'm thinking of using a TABLE with each cell being a 15 minute time block. Would it be easier to use DURATION or START/END_TIME to display this ? Actually, I reckon START/END_TIME would be easier.

I'll try it and come back here if I encounter any troubles.

Cheers again for your suggestions.