Event calendar database design using iCalendar in PHP and MYSQL

After long time we are publishing a new article about calendar events and multiple recurrence with mysql. So many free open source scripts are available for integrating calendar events. In this post explains how to integrate iCalendar event calendar script in PHP with MYSQL. Lets see database design things are

We have split two different ways of db structure one is a usual method and another one is a iCalendar method.

Old Method:

Notes: 

is_all_day – all day event (0-FALSE, 1-TRUE)

repeat_type – 1-daily, 2-weekly, 3-monthly, 4-yearly

interval – event duration.

Cons:

1. table dumping issue occurs.
2. complex query to use for get repeating events.
3. loading problem occurs in calendar.

Instead of this method to use iCalendar event scheduler.

iCalendar Method:

1. iCalendar globally declares some common objects to get and set variables with some specific values.
2. It supports both API and web platforms.
3. This calendar plugin support responsive.
4. Initially they provide some common add/edit/delete events popup functionalities in a calendar.
5. Simple recurring/repeating method for long time event tracking. It defines with rrule method.
6. Read more iCalendar specifications from here.

Define rrule:

rrule is nothing but recurring rule. It always start FREQ keyword. We have to define the values for it and read more structure from here.

For example it is a weekly event this should start every monday to friday means that

FREQ=WEEKLY;BYDAY=MO,TU,WE,TH,FR

For daily 10 times FREQ=DAILY;COUNT=10

For monthly 1st monday to start FREQ=MONTHLY;BYMONTHDAY=1

1. In this way to define daily, monthly and yearly too.
2. Database wise it stored in event table only one record.
3. So we can get those records with event start and end time values.
4. Query wise is also simple.
5. Read more recurring events click here. 

Now we create a database and below these tables.

Step 1: calendar table

CREATE TABLE `calendars` (
  `id` INT NOT NULL ,
  `title` VARCHAR(255) NOT NULL ,
  `color` SMALLINT NOT NULL DEFAULT 0 ,
  `hidden` TINYINT(1) NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;

Step 2: events table

CREATE  TABLE `events` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `title` VARCHAR(255) NOT NULL ,
  `start` DATETIME NOT NULL ,
  `end` DATETIME NOT NULL ,
  `all_day` TINYINT(1) NULL DEFAULT 0 ,
  `calendar_id` INT NULL ,
  `app_id` VARCHAR(20) NOT NULL ,
  `location` VARCHAR(255) NULL ,
  `notes` TEXT NULL ,
  `url` VARCHAR(255) NULL ,
  `reminder` VARCHAR(255) NULL ,
  `rrule` VARCHAR(999) NULL,
  `duration` INT NULL,

  PRIMARY KEY (`id`) ,
  INDEX `calendar_id_idx` (`calendar_id` ASC) ,
  CONSTRAINT `calendar_id`
    FOREIGN KEY (`calendar_id` )
    REFERENCES `calendars` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
) ENGINE=MyISAM AUTO_INCREMENT=44 DEFAULT CHARSET=latin1;

Download iCalendar event full script from our github page click here.

Right now we use iCalendar script and will add more related post to iCalendar functional wise in future.

Thanks for reading this post and let me know your feedback its very helpful to all readers.

Comments

comments