LogixMaster LogixMaster - 2 years ago 161
PHP Question

PHP-Calendar/schedule Database Structure

This is not usually the type of question I'd like to make on SO, but since I am finding it challenging and found no good resources on how to do this, here it goes..

I need to create a calendar which will be used to show scheduled appointments aswell as allow users to request appointments where there are available slots in a day. Practically I found one website which implements this calendar/scheduling system, very close to what I need.
Basically,the front side would be similar to this :

enter image description here

What I am having trouble is, since I am not a database master, I can't think of the best way on how to go about this. What sort of tables/fields would I need to allow such flexibility. I wouldn't have any trouble if the day wasn't split into hours and minutes, but this is a feature request. Do I need to have a table which represent a day with columns hours and minutes?

Any DB gurus that can provide further insight on how this can be done best?

NB. Every hour of the day is broken down into 4 quarters, since an appointment may take from 15 mins up to 45mins, depending on what type of appointment the user requests.

I do not need any db designs or anything, just some brief explanation that I could use as a starting point. I accept all constructive answers and will greatly be appreciated

Answer Source

You just have to store the event details in the table. In mysql I wolud create a table something like this:

CREATE TABLE `event` (
    `creator_user_id` INT UNSIGNED NOT NULL,
    `start_at` DATETIME NOT NULL,
    `finish_at` DATETIME NOT NULL,
    `location_id` INT UNSIGNED NOT NULL,
    `name` VARCHAR(50) NOT NULL,
    `description` TEXT NULL,
    PRIMARY KEY (`id`),
    INDEX `start_at` (`start_at`),
    INDEX `finish_at` (`finish_at`),
    INDEX `location_id` (`location_id`)

When displaying the calendar you just have to select only the records overlaping the displayed period.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download