Database Schema
Visual Studio project:
MementoDatabase
WorkLog
Stores the time, in minutes, of work completed on a particular task.
Column | Date Type | Allow Nulls | Description |
worklogId | int | No | primary key |
description | varchar | Yes | user entered description for the completed time |
minutes | decimal | No | user time completed stored as total minutes |
workdate | date | No | the date the work was done |
taskId * | int | No | |
personId * | int | No | |
categoryId * | int | No | |
datecreated | datetime | No | auto generated on row creation |
createdby | datetime | No | auto generated based on logged in user |
dateupdated | varchar | No | auto generated |
updated by | varchar | No | auto generated based on logged in user |
Project
Projects have sub-projects and tasks assigned to them. Time is booked to the task, not the project.
Column | Date Type | Allow Nulls | Description |
projectId | int | No | primary key |
name | varchar | No | name of the project |
description | varchar | Yes | description of the project |
sourcesystem | varchar | No | identifies the source system |
datecreated | datetime | No | auto generated on row creation |
createdby | datetime | No | auto generated based on logged in user |
dateupdated | varchar | No | auto generated |
updated by | varchar | No | auto generated based on logged in user |
Task
Tasks can have time booked against them.
Column | Date Type | Allow Nulls | Description |
taskId | int | No | primary key |
name | varchar | No | name of task |
description | varchar | Yes | description of task - may not be necessary |
projectId * | int | No | the project that owns this task |
datecreated | datetime | No | auto generated on row creation |
createdby | datetime | No | auto generated based on logged in user |
dateupdated | varchar | No | auto generated |
updated by | varchar | No | auto generated based on logged in user |
Person
Stores basic details of the person who is filling out the time.
Column | Date Type | Allow Nulls | Description |
personId | int | No | primary key |
firstname | varchar | No | |
lastname | varchar | No | |
email | varchar | Yes | |
datecreated | datetime | No | auto generated on row creation |
createdby | datetime | No | auto generated based on logged in user |
dateupdated | varchar | No | auto generated |
updated by | varchar | No | auto generated based on logged in user |
WorkCategory
Provides a many to many relationship between work logs and categories.
Column | Date Type | Allow Nulls | Description |
workcategoryId | int | No | primary key |
worklogId * | int | No | foreign key |
categoryId * | int | No | foreign key |
Category
Table used to assign categories to tasks. Primary use will be to filter reports on particular categories, i.e. billable / non-billable work.
Column | Date Type | Allow Nulls | Description |
categoryId | int | No | primary key |
categorygroupId * | int | No | foreign key |
name | varchar | No | category name |
description | varchar | Yes | category description |
datecreated | datetime | No | auto generated on row creation |
createdby | datetime | No | auto generated based on logged in user |
dateupdated | varchar | No | auto generated |
updated by | varchar | No | auto generated based on logged in user |
CategoryGroup
Provides a hierarchy for categories. All categories will belong to one, and only one category group.
Column | Date Type | Allow Nulls | Description |
categorygroupId | int | No | primary key |
name | varchar | No | the name of the group |