-- Database Design for a Task Management System
-- Tables:
-- Users table: Stores information about users.
CREATE TABLE Users (
UserID INT PRIMARY KEY AUTO_INCREMENT,
Username VARCHAR(255) UNIQUE NOT NULL,
Password VARCHAR(255) NOT NULL, -- In a real application, store
password hashes, not plain passwords.
Email VARCHAR(255) UNIQUE,
FullName VARCHAR(255),
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Projects table: Stores information about projects.
CREATE TABLE Projects (
ProjectID INT PRIMARY KEY AUTO_INCREMENT,
ProjectName VARCHAR(255) NOT NULL,
Description TEXT,
CreatedBy INT, -- User who created the project
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (CreatedBy) REFERENCES Users(UserID)
);
-- Tasks table: Stores information about individual tasks.
CREATE TABLE Tasks (
TaskID INT PRIMARY KEY AUTO_INCREMENT,
ProjectID INT,
Title VARCHAR(255) NOT NULL,
Description TEXT,
Status ENUM('Pending', 'In Progress', 'Completed') DEFAULT
'Pending', -- Task status
AssignedTo INT, -- User assigned to the task
DueDate DATE,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
FOREIGN KEY (AssignedTo) REFERENCES Users(UserID)
);
-- TaskComments table: Stores comments on tasks.
CREATE TABLE TaskComments (
CommentID INT PRIMARY KEY AUTO_INCREMENT,
TaskID INT,
UserID INT, -- User who posted the comment
CommentText TEXT,
CreatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (TaskID) REFERENCES Tasks(TaskID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
-- ProjectMembers table: Links users to projects (Many-to-Many
relationship).
CREATE TABLE ProjectMembers (
ProjectID INT,
UserID INT,
Role VARCHAR(255), -- e.g., 'Owner', 'Member', 'Viewer'
JoinedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (ProjectID, UserID), -- Composite key
FOREIGN KEY (ProjectID) REFERENCES Projects(ProjectID),
FOREIGN KEY (UserID) REFERENCES Users(UserID)
);
-- Sample Queries:
-- 1. Create a new user:
INSERT INTO Users (Username, Password, Email, FullName)
VALUES ('johndoe', 'hashed_password', '[Link]@[Link]', 'John
Doe'); -- Remember to hash passwords!
-- 2. Create a new project:
INSERT INTO Projects (ProjectName, Description, CreatedBy)
VALUES ('Website Redesign', 'Redesign the company website', 1); --
Assuming UserID 1 created it.
-- 3. Create a new task:
INSERT INTO Tasks (ProjectID, Title, Description, AssignedTo, DueDate)
VALUES (1, 'Design Mockups', 'Create mockups for the new website
design', 2, '2024-03-15'); -- Assuming ProjectID 1 and UserID 2.
-- 4. Add a user to a project:
INSERT INTO ProjectMembers (ProjectID, UserID, Role)
VALUES (1, 2, 'Member');
-- 5. Get all tasks for a project:
SELECT * FROM Tasks WHERE ProjectID = 1;
-- 6. Get tasks assigned to a specific user:
SELECT * FROM Tasks WHERE AssignedTo = 2;
-- 7. Get tasks due in the next week:
SELECT * FROM Tasks WHERE DueDate BETWEEN CURDATE() AND
DATE_ADD(CURDATE(), INTERVAL 7 DAY);
-- 8. Add a comment to a task:
INSERT INTO TaskComments (TaskID, UserID, CommentText)
VALUES (1, 2, 'These mockups look great!');
-- 9. Get all comments for a task:
SELECT * FROM TaskComments WHERE TaskID = 1;
-- 10. Update task status:
UPDATE Tasks SET Status = 'In Progress' WHERE TaskID = 1;
-- 11. Get projects a user is a member of:
SELECT p.*
FROM Projects p
JOIN ProjectMembers pm ON [Link] = [Link]
WHERE [Link] = 2;