1 pjaol 1.3 -- $Id: gantt_manager.sql,v 1.2 2005/01/17 23:15:41 pjaol Exp $
  2           -- $Name:  $
  3           CREATE TABLE dependancyType (
  4             idDependancyType INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  5             name VARCHAR(20) NULL,
  6             PRIMARY KEY(idDependancyType)
  7           );
  8           
  9           CREATE TABLE Groups (
 10             idGroups INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 11             groupName VARCHAR(20) NULL,
 12             PRIMARY KEY(idGroups)
 13           )
 14           TYPE=InnoDB;
 15           
 16           CREATE TABLE projects (
 17             idprojects INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 18             Groups_idGroups INTEGER UNSIGNED NOT NULL,
 19             projectName VARCHAR(20) NULL,
 20             startDate DATETIME NULL,
 21             endDate DATETIME NULL,
 22 pjaol 1.3   slipage DATETIME NULL,
 23             PRIMARY KEY(idprojects),
 24             INDEX projects_FKIndex2(Groups_idGroups),
 25             FOREIGN KEY(Groups_idGroups)
 26               REFERENCES Groups(idGroups)
 27                 ON DELETE NO ACTION
 28                 ON UPDATE NO ACTION
 29           )
 30           TYPE=InnoDB;
 31           
 32           CREATE TABLE groupUsers (
 33             idGroupUsers INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 34             Groups_idGroups INTEGER UNSIGNED NOT NULL,
 35             userName VARCHAR(20) NULL,
 36             fullName VARCHAR(45) NULL,
 37             PRIMARY KEY(idGroupUsers),
 38             INDEX groupUsers_FKIndex1(Groups_idGroups),
 39             FOREIGN KEY(Groups_idGroups)
 40               REFERENCES Groups(idGroups)
 41                 ON DELETE NO ACTION
 42                 ON UPDATE NO ACTION
 43 pjaol 1.3 )
 44           TYPE=InnoDB;
 45           
 46           CREATE TABLE Activity (
 47             idActivity INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 48             groupUsers_idGroupUsers INTEGER UNSIGNED NOT NULL,
 49             Groups_idGroups INTEGER UNSIGNED NOT NULL,
 50             projects_idprojects INTEGER UNSIGNED NOT NULL,
 51             activityName VARCHAR(20) NULL,
 52             activityDesc VARCHAR(255) NULL,
 53             startDate DATETIME NULL,
 54             endDate DATETIME NULL,
 55             slipage DATETIME NULL,
 56             PRIMARY KEY(idActivity),
 57             INDEX Activity_FKIndex1(projects_idprojects),
 58             INDEX Activity_FKIndex2(Groups_idGroups),
 59             INDEX Activity_FKIndex3(groupUsers_idGroupUsers),
 60             FOREIGN KEY(projects_idprojects)
 61               REFERENCES projects(idprojects)
 62                 ON DELETE NO ACTION
 63                 ON UPDATE NO ACTION,
 64 pjaol 1.3   FOREIGN KEY(Groups_idGroups)
 65               REFERENCES Groups(idGroups)
 66                 ON DELETE NO ACTION
 67                 ON UPDATE NO ACTION,
 68             FOREIGN KEY(groupUsers_idGroupUsers)
 69               REFERENCES groupUsers(idGroupUsers)
 70                 ON DELETE NO ACTION
 71                 ON UPDATE NO ACTION
 72           )
 73           TYPE=InnoDB;
 74           
 75           CREATE TABLE diary (
 76             iddiary INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 77             Activity_idActivity INTEGER UNSIGNED NOT NULL,
 78             diaryType VARCHAR(20) NULL,
 79             description VARCHAR(20) NULL,
 80             message VARCHAR(255) NULL,
 81             date DATETIME NULL,
 82             PRIMARY KEY(iddiary),
 83             INDEX diary_FKIndex1(Activity_idActivity),
 84             FOREIGN KEY(Activity_idActivity)
 85 pjaol 1.3     REFERENCES Activity(idActivity)
 86                 ON DELETE NO ACTION
 87                 ON UPDATE NO ACTION
 88           );
 89           
 90           CREATE TABLE dependacies (
 91             idDependacies INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
 92             Activity_idActivity INTEGER UNSIGNED NOT NULL,
 93             dependancyType_idDependancyType INTEGER UNSIGNED NOT NULL,
 94             PRIMARY KEY(idDependacies),
 95             INDEX dependacies_FKIndex1(dependancyType_idDependancyType),
 96             INDEX dependacies_FKIndex2(Activity_idActivity),
 97             FOREIGN KEY(dependancyType_idDependancyType)
 98               REFERENCES dependancyType(idDependancyType)
 99                 ON DELETE NO ACTION
100                 ON UPDATE NO ACTION,
101             FOREIGN KEY(Activity_idActivity)
102               REFERENCES Activity(idActivity)
103                 ON DELETE NO ACTION
104                 ON UPDATE NO ACTION
105           );
106 pjaol 1.3 
107