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

Last edited May 13, 2013 at 8:09 PM by mattsalmon, version 16

Comments

No comments yet.