I've got tired of using notepad / excel to manage my tasks at work.
Since tasks may sometimes contain sensitive data, my boss will not allow us to use an online task management system, so I've decided to create one myself.
I'm not looking for tfs backlog style, I want to keep it as simple as possible. my goal is that everyone can add/edit/delete tasks that is associated with them, can sort tasks by priority, hide completed tasks, etc. nothing too fancy.
Here is my initial tables design, please let me know if I'm missing something critical, or if you have any suggestions to make it better.
CREATE TABLE tblProject
(
Project_Id int identity(1,1) primary key,
Prject_Name varchar(300) not null,
CONSTRAINT uc_tblProject_Prject_Name UNIQUE(Prject_Name)
)
CREATE TABLE tblUser
(
User_Id int identity(1,1) primary key,
User_First_Name varchar(30) not null,
User_Last_Name varchar(30) not null,
User_login_Name varchar(10) not null,
User_Password varchar(6) not null,
CONSTRAINT uc_tblUser_User_login_Name UNIQUE(User_login_Name)
)
CREATE TABLE tblUserToProject
(
UTP_User_Id int,
UTP_Project_Id int,
primary key(UTP_User_Id, UTP_Project_Id)
)
CREATE TABLE tblTaskStatus
(
Task_Status_Id int identity(1,1) primary key,
Task_Status_Name varchar(30) not null,
CONSTRAINT uc_tblTaskStatus_Task_Status_Name UNIQUE(Task_Status_Name)
)
CREATE TABLE tblTaskPriority
(
Task_Priority_Id int identity(1,1) primary key,
Task_Priority_Name varchar(30) not null,
CONSTRAINT uc_tblTaskPriority_Priority_Name UNIQUE(Task_Priority_Name)
)
CREATE TABLE tblTask
(
Task_Id int identity(1,1) primary key,
Task_User_Id int,
Task_Status int,
Task_Title varchar(300),
Task_Description varchar(max),
Task_Remarks varchar(500),
Task_Project int,
Task_Parent int null,
Task_Priority int,
Task_Create_Date datetime,
Task_Due_Date datetime,
Task_Time_Estimate int, -- (minutes)
Task_Actual_Time int -- (minutes)
)
CREATE TABLE TblTaskActivityLog
(
TaskHistory_Id int identity(1,1) primary key,
TaskHistory_Task_Id int,
TaskHistory_CreateDate int,
TaskHistory_Description varchar(max)
)