CREATE TABLE projects (
project_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
leader_id INT,
status ENUM('pending', 'in progress', 'completed') DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
]]>
CREATE TABLE members (
member_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
email VARCHAR(255),
phone VARCHAR(20),
department VARCHAR(255)
);
]]>
CREATE TABLE project_members (
project_id INT,
member_id INT,
role ENUM('leader', 'member'),
FOREIGN KEY (project_id) REFERENCES projects(project_id),
FOREIGN KEY (member_id) REFERENCES members(member_id),
PRIMARY KEY (project_id, member_id)
);
]]>
CREATE TABLE tasks (
task_id INT PRIMARY KEY AUTO_INCREMENT,
project_id INT,
title VARCHAR(255) NOT NULL,
description TEXT,
assigned_to INT,
due_date DATE,
completed BOOLEAN DEFAULT FALSE,
FOREIGN KEY (project_id) REFERENCES projects(project_id),
FOREIGN KEY (assigned_to) REFERENCES members(member_id)
);
]]>