-- MySQL dump 9.06

-- Host: localhost Database: apprentice

--
-- Current Database: apprentice
--

CREATE DATABASE /!32312 IF NOT EXISTS/ apprentice;

USE apprentice;

--
-- Table structure for table 'Category'
--

CREATE TABLE Category (
category_id int(10) unsigned NOT NULL auto_increment,
name varchar(100) NOT NULL default '',
PRIMARY KEY (category_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'Category'
--

INSERT INTO Category VALUES (1,'Database');
INSERT INTO Category VALUES (2,'Bio-informatics');
INSERT INTO Category VALUES (3,'Web');
INSERT INTO Category VALUES (4,'XML');
INSERT INTO Category VALUES (5,'mod_perl');
INSERT INTO Category VALUES (6,'System administration');
INSERT INTO Category VALUES (7,'GUI apps');

--
-- Table structure for table 'Project'
--

CREATE TABLE Project (
project_id int(10) unsigned NOT NULL auto_increment,
name varchar(200) NOT NULL default '',
description text NOT NULL,
creation_date datetime NOT NULL default '0000-00-00 00:00:00',
difficulty tinyint(3) unsigned NOT NULL default '0',
project_status_id tinyint(3) unsigned NOT NULL default '0',
project_support_level_id tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (project_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'Project'
--

--
-- Table structure for table 'ProjectCategory'
--

CREATE TABLE ProjectCategory (
project_id int(11) NOT NULL default '0',
category_id int(11) NOT NULL default '0',
PRIMARY KEY (project_id,category_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProjectCategory'
--

--
-- Table structure for table 'ProjectLink'
--

CREATE TABLE ProjectLink (
project_id int(10) unsigned NOT NULL default '0',
url varchar(200) NOT NULL default '',
description varchar(200) NOT NULL default '',
project_link_id int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (project_link_id),
KEY ProjectLink___project_id (project_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProjectLink'
--

--
-- Table structure for table 'ProjectMember'
--

CREATE TABLE ProjectMember (
project_id int(11) NOT NULL default '0',
user_id int(11) NOT NULL default '0',
role_id tinyint(3) unsigned NOT NULL default '0',
is_project_admin tinyint(4) NOT NULL default '0',
PRIMARY KEY (project_id,user_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProjectMember'
--

--
-- Table structure for table 'ProjectStatus'
--

CREATE TABLE ProjectStatus (
project_status_id tinyint(3) unsigned NOT NULL auto_increment,
status varchar(10) NOT NULL default '',
PRIMARY KEY (project_status_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProjectStatus'
--

INSERT INTO ProjectStatus VALUES (1,'Idea');
INSERT INTO ProjectStatus VALUES (2,'Active');

--
-- Table structure for table 'ProjectSupportLevel'
--

CREATE TABLE ProjectSupportLevel (
project_support_level_id tinyint(3) unsigned NOT NULL auto_increment,
support_level varchar(10) NOT NULL default '',
PRIMARY KEY (project_support_level_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'ProjectSupportLevel'
--

INSERT INTO ProjectSupportLevel VALUES (1,'A lot');
INSERT INTO ProjectSupportLevel VALUES (2,'Some');
INSERT INTO ProjectSupportLevel VALUES (3,'Little');

--
-- Table structure for table 'Role'
--

CREATE TABLE Role (
role_id tinyint(3) unsigned NOT NULL auto_increment,
role varchar(15) NOT NULL default '',
PRIMARY KEY (role_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'Role'
--

INSERT INTO Role VALUES (1,'Mentor');
INSERT INTO Role VALUES (2,'Apprentice');

--
-- Table structure for table 'User'
--

CREATE TABLE User (
user_id int(10) unsigned NOT NULL auto_increment,
username varchar(30) NOT NULL default '',
password varchar(100) NOT NULL default '',
real_name varchar(75) NOT NULL default '',
email_address varchar(150) NOT NULL default '',
user_status_id tinyint(3) unsigned default NULL,
is_admin tinyint(4) NOT NULL default '0',
PRIMARY KEY (user_id),
UNIQUE KEY User___username (username)
) TYPE=MyISAM;

--
-- Dumping data for table 'User'
--

INSERT INTO User VALUES (1,'admin','fayewong','Faye Wong','faye@wong',NULL,1);
INSERT INTO User VALUES (2,'joe','joeofclew','joe speigle','joeofclew@yahoo.com',1,0);

--
-- Table structure for table 'UserStatus'
--

CREATE TABLE UserStatus (
user_status_id tinyint(3) unsigned NOT NULL auto_increment,
status varchar(30) NOT NULL default '',
PRIMARY KEY (user_status_id)
) TYPE=MyISAM;

--
-- Dumping data for table 'UserStatus'
--

INSERT INTO UserStatus VALUES (1,'Available');
INSERT INTO UserStatus VALUES (2,'Semi-available');
INSERT INTO UserStatus VALUES (3,'Busy');