0

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)
)
3
  • In each of your identity, primary key columns I would also make them the clustering key - just change primary key to primary key clustered and it will automatically create one for you. I would also suggest adding the foreign keys (Task Status, Priority, etc....). Without a bit more information for what you are doing those are the glaring things in my eyes.
    – mskinner
    Commented Jul 21, 2015 at 20:39
  • @mskinner thanks. I should have stated that it's just an initial sketch... of cource I will use foreign keys (though you didn't have any way of knowing that from the question). Thanks for the clustered tip, I thought primary keys are clustered by default, perhaps only when you create the table via ssms designer? Commented Jul 22, 2015 at 3:56
  • You are absolutely correct, creating the Primary Key will automatically create you a unique clustered index on that key. It is just a preference so that it is clearly shown which one you intend to be the clustering key but certainly not necessary.
    – mskinner
    Commented Jul 22, 2015 at 14:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.