# $Id: create_tables-0.8.3.mysql,v 1.1 2001/02/26 03:02:04 bettina Exp $

 CREATE TABLE p_projects (
	id		int(11) NOT NULL auto_increment,
	num		varchar(20) NOT NULL,
	owner		int(11),
	access		varchar(25),
	entry_date	int(11),
	date		int(11),
        end_date        int(11),
	coordinator	int(11),
	customer	int(11),
        status		enum('active','nonactive','archiv') NOT NULL,
	descr		text,
	title		varchar(50),
	budget		decimal(20,2),
	PRIMARY KEY (id)
);

CREATE TABLE p_activities (
	id		int(11) NOT NULL auto_increment,
	num		varchar(20),
	descr		varchar(50),
	remarkreq	enum('N','Y') NOT NULL,
	minperae	decimal(4,0),
	billperae	decimal(20,2),
	PRIMARY KEY (id)
);

CREATE TABLE p_projectactivities (
	id		int(11) NOT NULL auto_increment,
	project_id	int(11),
	activity_id	int(11),
	billable	enum('N','Y') NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE p_hours (
	id		int(11) NOT NULL auto_increment,
	employee	int(11) NOT NULL,
	project_id	int(11),
	activity_id	int(11),
	entry_date	int(11),
	date		int(11),
        end_date        int(11),
	remark		text,
	minutes		int(10),
	minperae	decimal(4,0),
	billperae	decimal(20,2),
	status		enum('done','open','billed') NOT NULL,
	PRIMARY KEY (id)
);

CREATE TABLE p_projectmembers (
	id		int(11) NOT NULL auto_increment,
	project_id	int(11),
	account_id	int(11),
	PRIMARY KEY (id)
);

CREATE TABLE p_invoice (
	id		int(11) NOT NULL auto_increment,
	num		varchar(11) NOT NULL,
	date		int(11),
	project_id	int(11),
	customer	int(11),
	sum		decimal(20,2),
	PRIMARY KEY (id),
	UNIQUE (num)
);

CREATE TABLE p_invoicepos (
	id		int(11) NOT NULL auto_increment,
	invoice_id	int(11) NOT NULL,
	hours_id	int(11),
	PRIMARY KEY (id)
);

CREATE TABLE p_delivery (
	id		int(11) NOT NULL auto_increment,
	num		varchar(11) NOT NULL,
	date		int(11),
	project_id	int(11),
	customer	int(11),
	PRIMARY KEY (id),
	UNIQUE (num)
);

CREATE TABLE p_deliverypos (
	id		int(11) NOT NULL auto_increment,
	delivery_id	int(11) NOT NULL,
	hours_id	int(11),
	PRIMARY KEY (id)
);

create unique index project_num on p_projects (num);
