ess.sql

-- MySQL dump 9.10
--
-- Host: localhost    Database: mpac
-- ------------------------------------------------------
-- Server version   4.1.6-gamma-standard

--
-- Table structure for table `areas`
--

CREATE TABLE areas (
  area_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  area_name VARCHAR(20) DEFAULT NULL,
  area_desc VARCHAR(100) NOT NULL DEFAULT '',
  area_templ text,
  PRIMARY KEY  (area_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `areasuper`
--

CREATE TABLE areasuper (
  as_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  as_area_id INT(11) UNSIGNED NOT NULL DEFAULT '0',
  as_uid INT(11) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (as_id),
  KEY as_area_id (as_area_id),
  KEY as_uid (as_uid),
  CONSTRAINT areasuper_ibfk_1 FOREIGN KEY (as_area_id) REFERENCES areas (area_id) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT areasuper_ibfk_2 FOREIGN KEY (as_uid) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `assignments`
--

CREATE TABLE assignments (
  assign_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  assign_uid INT(10) UNSIGNED NOT NULL DEFAULT '0',
  assign_pid INT(10) UNSIGNED NOT NULL DEFAULT '0',
  assign_eid INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY  (assign_id),
  KEY assign_uid (assign_uid),
  KEY assign_pid (assign_pid),
  KEY assign_eid (assign_eid),
  CONSTRAINT assignments_ibfk_1 FOREIGN KEY (assign_uid) REFERENCES users (user_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT assignments_ibfk_2 FOREIGN KEY (assign_pid) REFERENCES positions (pos_id) ON DELETE NO ACTION ON UPDATE CASCADE,
  CONSTRAINT assign_assign_eid_fk FOREIGN KEY (assign_eid) REFERENCES `events` (event_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `availabletimes`
--

CREATE TABLE availabletimes (
  avail_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  avail_uid INT(11) UNSIGNED NOT NULL DEFAULT '0',
  avail_day CHAR(1) NOT NULL DEFAULT '',
  avail_start TIME NOT NULL DEFAULT '00:00:00',
  avail_end TIME NOT NULL DEFAULT '00:00:00',
  PRIMARY KEY  (avail_id),
  KEY avail_uid (avail_uid),
  CONSTRAINT availabletimes_ibfk_1 FOREIGN KEY (avail_uid) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `dayoff`
--

CREATE TABLE dayoff (
  day_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  day_uid INT(11) UNSIGNED NOT NULL DEFAULT '0',
  day_start DATE NOT NULL DEFAULT '0000-00-00',
  day_end DATE NOT NULL DEFAULT '0000-00-00',
  day_desc VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY  (day_id),
  KEY day_uid (day_uid),
  CONSTRAINT dayoff_ibfk_1 FOREIGN KEY (day_uid) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  event_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  event_start TIME NOT NULL DEFAULT '00:00:00',
  event_end TIME NOT NULL DEFAULT '00:00:00',
  event_area_id INT(11) UNSIGNED NOT NULL DEFAULT '0',
  event_name VARCHAR(50) NOT NULL DEFAULT '',
  event_comments text,
  event_date DATE NOT NULL DEFAULT '0000-00-00',
  PRIMARY KEY  (event_id),
  KEY event_area_id (event_area_id),
  CONSTRAINT events_ibfk_1 FOREIGN KEY (event_area_id) REFERENCES areas (area_id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `positions`
--

CREATE TABLE positions (
  pos_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  pos_area_id INT(10) UNSIGNED NOT NULL DEFAULT '0',
  pos_name VARCHAR(20) NOT NULL DEFAULT '',
  pos_desc VARCHAR(100) NOT NULL DEFAULT '',
  PRIMARY KEY  (pos_id),
  KEY pos_area_id (pos_area_id),
  CONSTRAINT positions_ibfk_1 FOREIGN KEY (pos_area_id) REFERENCES areas (area_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `supervisors`
--

CREATE TABLE supervisors (
  super_emp INT(11) UNSIGNED NOT NULL DEFAULT '0',
  super_super INT(11) UNSIGNED NOT NULL DEFAULT '0',
  KEY super_emp (super_emp),
  CONSTRAINT supervisors_ibfk_1 FOREIGN KEY (super_emp) REFERENCES users (user_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `types`
--

CREATE TABLE `types` (
  type_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  type_name VARCHAR(10) NOT NULL DEFAULT '',
  PRIMARY KEY  (type_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Table structure for table `users`
--

CREATE TABLE users (
  user_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  user_name VARCHAR(10) NOT NULL DEFAULT '',
  user_pass VARCHAR(40) NOT NULL DEFAULT '',
  user_first VARCHAR(10) NOT NULL DEFAULT '',
  user_last VARCHAR(20) NOT NULL DEFAULT '',
  user_email VARCHAR(50) NOT NULL DEFAULT '',
  user_phone1 BIGINT(20) NOT NULL DEFAULT '0',
  user_phone2 BIGINT(20) DEFAULT NULL,
  user_type INT(11) UNSIGNED NOT NULL DEFAULT '0',
  user_pay_rate FLOAT(5,2) DEFAULT '0.00',
  PRIMARY KEY  (user_id),
  KEY user_type (user_type),
  CONSTRAINT users_ibfk_1 FOREIGN KEY (user_type) REFERENCES `types` (type_id) ON DELETE NO ACTION ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;