[Planner Dev] MySQL tables

[Lars Brandi Jensen <larspriv abj dk>]

Hello

I have made a draft for MySQL tables. It is importing well to MySQL 
4.0.1, but I know there is something new in 4.1.0 like support for 
boolean. A review of the structure would be nice :

# MySQL Navigator Xport
# Database: planner
# root localhost

# CREATE DATABASE planner;
# USE planner;

#
# Table structure for table 'allocation'
#

# DROP TABLE IF EXISTS allocation;
CREATE TABLE `allocation` (
 `task_id` int(11) NOT NULL default '0',
 `res_id` int(11) NOT NULL auto_increment,
 `units` double NOT NULL default '0',
 KEY `res_id` (`res_id`)
) TYPE=MyISAM;

#
# Table structure for table 'calendar'
#

# DROP TABLE IF EXISTS calendar;
CREATE TABLE `calendar` (
 `cal_id` int(11) NOT NULL auto_increment,
 `proj_id` int(11) default NULL,
 `parent_cid` int(11) default NULL,
 `name` text,
 `day_mon` int(11) default NULL,
 `day_tue` int(11) default NULL,
 `day_wed` int(11) default NULL,
 `day_thu` int(11) default NULL,
 `day_fri` int(11) default NULL,
 `day_sat` int(11) default NULL,
 `day_sun` int(11) default NULL,
 KEY `cal_id` (`cal_id`)
) TYPE=MyISAM;

#
# Table structure for table 'day'
#

# DROP TABLE IF EXISTS day;
CREATE TABLE `day` (
 `day_id` int(11) NOT NULL auto_increment,
 `cal_id` int(11) default NULL,
 `dtype_id` int(11) default NULL,
 `date` date default NULL,
 KEY `day_id` (`day_id`)
) TYPE=MyISAM;

#
# Table structure for table 'day_interval'
#

# DROP TABLE IF EXISTS day_interval;
CREATE TABLE `day_interval` (
 `cal_id` int(11) NOT NULL default '0',
 `dtype_id` int(11) NOT NULL default '0',
 `start_time` time NOT NULL default '00:00:00',
 `end_time` time NOT NULL default '00:00:00'
) TYPE=MyISAM;

#
# Table structure for table 'daytype'
#

# DROP TABLE IF EXISTS daytype;
CREATE TABLE `daytype` (
 `dtype_id` int(11) NOT NULL default '0',
 `proj_id` int(11) NOT NULL auto_increment,
 `name` text,
 `descr` text,
 `is_work` tinyint(4) default '1',
 `is_nonwork` tinyint(4) default NULL,
 KEY `proj_id` (`proj_id`)
) TYPE=MyISAM;

#
# Table structure for table 'phase'
#

# DROP TABLE IF EXISTS phase;
CREATE TABLE `phase` (
 `phase_id` int(11) NOT NULL auto_increment,
 `proj_id` int(11) default NULL,
 `name` text NOT NULL,
 KEY `phase_id` (`phase_id`)
) TYPE=MyISAM;

#
# Table structure for table 'predecessor'
#

# DROP TABLE IF EXISTS predecessor;
CREATE TABLE `predecessor` (
 `task_id` int(11) NOT NULL auto_increment,
 `pred_task_id` int(11) NOT NULL default '0',
 `pred_id` int(11) default NULL,
 `type` text NOT NULL,
 `lag` int(11) default NULL,
 KEY `task_id` (`task_id`)
) TYPE=MyISAM;

#
# Table structure for table 'project'
#

# DROP TABLE IF EXISTS project;
CREATE TABLE `project` (
 `proj_id` int(11) NOT NULL auto_increment,
 `name` text NOT NULL,
 `company` text,
 `manager` text,
 `proj_start` date NOT NULL default '0000-00-00',
 `cal_id` int(11) default NULL,
 `phase` text,
 `default_group_id` int(11) default NULL,
 `revision` int(11) default NULL,
 `last_user` text NOT NULL,
 KEY `proj_id` (`proj_id`)
) TYPE=MyISAM;

#
# Table structure for table 'project_to_property'
#

# DROP TABLE IF EXISTS project_to_property;
CREATE TABLE `project_to_property` (
 `proj_id` int(11) NOT NULL default '0',
 `prop_id` int(11) NOT NULL default '0'
) TYPE=MyISAM;

#
# Table structure for table 'property'
#

# DROP TABLE IF EXISTS property;
CREATE TABLE `property` (
 `prop_id` int(11) NOT NULL auto_increment,
 `proptype_id` int(11) NOT NULL default '0',
 `value` text,
 KEY `prop_id` (`prop_id`)
) TYPE=MyISAM;

#
# Table structure for table 'property_type'
#

# DROP TABLE IF EXISTS property_type;
CREATE TABLE `property_type` (
 `proptype_id` int(11) NOT NULL auto_increment,
 `proj_id` int(11) default NULL,
 `name` text NOT NULL,
 `label` text NOT NULL,
 `type` text NOT NULL,
 `owner` text NOT NULL,
 `descr` text,
 KEY `proptype_id` (`proptype_id`)
) TYPE=MyISAM;

#
# Table structure for table 'resource'
#

# DROP TABLE IF EXISTS resource;
CREATE TABLE `resource` (
 `res_id` int(11) NOT NULL auto_increment,
 `proj_id` int(11) NOT NULL default '0',
 `group_id` int(11) default NULL,
 `name` text,
 `email` text,
 `note` text,
 `is_worker` tinyint(4) NOT NULL default '0',
 `units` double NOT NULL default '0',
 `std_rate` double NOT NULL default '0',
 `ovt_rate` double NOT NULL default '0',
 `cal_id` int(11) default NULL,
 KEY `res_id` (`res_id`)
) TYPE=MyISAM;

#
# Table structure for table 'resource_group'
#

# DROP TABLE IF EXISTS resource_group;
CREATE TABLE `resource_group` (
 `group_id` int(11) NOT NULL auto_increment,
 `proj_id` int(11) default NULL,
 `name` text NOT NULL,
 `admin_name` text,
 `admin_phone` text,
 `admin_email` text,
 KEY `group_id` (`group_id`)
) TYPE=MyISAM;

#
# Table structure for table 'resource_to_property'
#

# DROP TABLE IF EXISTS resource_to_property;
CREATE TABLE `resource_to_property` (
 `prop_id` int(11) NOT NULL default '0',
 `res_id` int(11) NOT NULL auto_increment,
 KEY `res_id` (`res_id`)
) TYPE=MyISAM;

#
# Table structure for table 'task'
#

# DROP TABLE IF EXISTS task;
CREATE TABLE `task` (
 `task_id` int(11) NOT NULL auto_increment,
 `parent_id` int(11) default NULL,
 `proj_id` int(11) default NULL,
 `name` text NOT NULL,
 `note` text,
 `start` timestamp(14) NOT NULL,
 `finish` timestamp(14) NOT NULL,
 `work` int(11) default NULL,
 `duration` int(11) default NULL,
 `percent_complete` int(11) default NULL,
 `is_milestone` tinyint(4) NOT NULL default '0',
 `is_fixed_work` tinyint(4) NOT NULL default '0',
 `constraint_type` text NOT NULL,
 `constraint_time` timestamp(14) NOT NULL,
 KEY `task_id` (`task_id`)
) TYPE=MyISAM;

#
# Table structure for table 'task_to_property'
#

# DROP TABLE IF EXISTS task_to_property;
CREATE TABLE `task_to_property` (
 `prop_id` int(11) NOT NULL default '0',
 `task_id` int(11) NOT NULL auto_increment,
 KEY `task_id` (`task_id`)
) TYPE=MyISAM;


Lars Brandi Jensen