How to optimise job conflict

Hi,

I have written a web based calendaring application for regularly occuring jobs.

I currently have the problem that I can't figure out a good query for finding conflicts for all jobs of a contract (defining the regularity).

I have used a calendar table for the week view of this application, but this doesn't perform well on jobs, which are in contracts. This is probably due to that the jobs are spread over a large part of the calendar table, i.e. every week for 5 years.

Anyone has any ideas of how to solve this with the database. I have a client side scripting solution now, but do generally prefer database (data model) based solutions.


Thanks for any help.

Jochen

SQL dump for calendar and jobs:
(this is mysql 4.0)

REATE TABLE `calendar` (
`cal_datetime` datetime NOT NULL default '0000-00-00 00:00:00',
`cal_time` time NOT NULL default '00:00:00',
`cal_weekno` tinyint(4) NOT NULL default '0',
`cal_dayofweek` tinyint(4) NOT NULL default '0',
`cal_dayofmonth` tinyint(4) NOT NULL default '0',
`cal_weekofmonth` tinyint(4) NOT NULL default '0',
`cal_is_last_weekday_of_month` tinyint(4) NOT NULL default '0',
`cal_is_2last_weekday_of_month` tinyint(4) NOT NULL default '0',
`cal_is_stat_holiday` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`cal_datetime`),
KEY `cal_dayofweek` (`cal_dayofweek`),
KEY `cal_time` (`cal_time`),
KEY `cal_dayofmonth` (`cal_dayofmonth`),
KEY `cal_weekofmonth` (`cal_weekofmonth`)
) TYPE=MyISAM;

-- --------------------------------------------------------

--
-- Table structure for table `job`
--

CREATE TABLE `job` (
`job_id` int(11) NOT NULL auto_increment,
`job_cntr_id` int(11) NOT NULL default '0',
`job_schd_id` int(11) default NULL,
`job_user_id` int(11) NOT NULL default '0',
`job_status` tinyint(4) default NULL,
`job_mved_from_job_id` int(11) NOT NULL default '0',
`job_mved_to_job_id` int(11) NOT NULL default '0',
`job_cntr_plnd_start` datetime NOT NULL default '0000-00-00 00:00:00',
`job_cntr_plnd_finsh` datetime NOT NULL default '0000-00-00 00:00:00',
`job_oper_plnd_start` datetime NOT NULL default '0000-00-00 00:00:00',
`job_oper_plnd_finsh` datetime NOT NULL default '0000-00-00 00:00:00',
`job_actl_start` datetime default NULL,
`job_actl_finsh` datetime default NULL,
`job_billed_hours` decimal(5,2) NOT NULL default '0.00',
`job_planned_hours` decimal(5,2) NOT NULL default '0.00',
`job_actl_hours` decimal(5,2) default NULL,
`job_name` varchar(50) default NULL,
`job_description` mediumblob,
`job_requirements` mediumblob,
`job_checks` varchar(20) default NULL,
`job_last_update` date NOT NULL default '1970-01-01',
`job_type` tinyint(4) NOT NULL default '0',
`job_in_clipboard` tinyint(4) NOT NULL default '0',
PRIMARY KEY (`job_id`),
KEY `job_cntr_id` (`job_cntr_id`),
KEY `job_schd_id` (`job_schd_id`),
KEY `job_oper_plnd_start` (`job_oper_plnd_start`),
KEY `job_oper_plnd_finsh` (`job_oper_plnd_finsh`),
KEY `job_oper_plnd_start_2` (`job_oper_plnd_start`,`job_oper_plnd_finsh`)
) TYPE=MyISAM AUTO_INCREMENT=25198 ;