Time Tracker Database schema

We have chosen to use PostgreSQL Database as our current database provider. This may change in the future, but it allows us to get development off the ground until we get to the point where we know the exact database provider we will be working with. The database schema is generated using Entity Framework Core Code First Database Migrations, or in short Database Migrations. Each of the migration specifies how the queries that need to be ran to get the database to a specific point. You can learn more about this in our Entity Framework Core chapter.

It is likely we will end up using SQL Server in the future. As if I remember correctly the Weber State databases are also on SQL Server so it would allow better compatibility there.

The below documentation reflects the state of the database that we would like to get to not necessarily the current state of the database. To find the current state of the database look at the migrations and or connect to the database and generate a relationship diagram. Most tools can build diagrams from the database, I personally use DataGrip, however SSMS should be able to do that as well.

• Database Tables
• TimeTracker ERD

---

Tables

• TimeCard System

  • Courses
  • Group
  • Project
  • TimeCard
  • User
  • UserCourse
  • UserGroup

• Evaluation System

  • Eval
  • EvalResponse
  • EvalTemplate
  • EvalTemplateQuestion
  • EvalTemplateQuestionCategory

Courses

Key	Name	Data Type	Nullable	Description / Attributes
PK	courseID	Int		Identity / Auto Increment column
FK	InstructorID	Int		User ID
FK	courseName	Nvarchar(50)		Course Name
	description	Nvarchar(MAX)	Y	Course Description
	isActive	boolean		Active Course, Default: 1

Eval

Key	Name	Data Type	Nullable	Description / Attributes
PK	evalID	Int		Identity / Auto Increment column
FK	userID	Int		User ID
FK	groupID	Int		Group ID
FK	evalTemplateID	Int		Evaluation Template ID
	number	Int	Y	Evaluation Number
	isComplete	Bit		Is/IsNot Complete, default: 0

EvalTemplate

Key	Name	Data Type	Nullable	Description / Attributes
PK	evalTemplateID	Int		Identity / Auto Increment column
FK	userID	Int		User ID
	templateName	Varchar(50)	Y	Evaluation Template Name

EvalResponse

Key	Name	Data Type	Nullable	Description / Attributes
PK	evalResponseID	Int		Identity / Auto Increment column
FK	evalID	Int		Evaluation ID
FK	evalTemplateQuestionID	Int		Evaluation Template Question ID
FK	userID	Int		User ID

EvalTemplateQuestion

Key	Name	Data Type	Nullable	Description / Attributes
PK	evalTemplateQuestionID	Int		Identity / Auto Increment column
FK	evalTemplateID	Int		Evaluation Template ID
FK	evalTemplatQuestionCategoryID	Int		Evaluation Template Question Category ID
	questionType	Varchar(50)	Y	Question Type
	questionText	Varchar(MAX)	Y	Question Body
	number	Int	Y	Number

EvalTemplateQuestionCategory

Key	Name	Data Type	Nullable	Description / Attributes
PK	evalTemplateQuestionCategoryID	Int		Identity / Auto Increment column
FK	evalTemplateID	Int		Evaluation Template ID
	categoryName	Varchar(50)	Y	Question Category

Groups

Key	Name	Data Type	Nullable	Description / Attributes
PK	groupID	Int		Identity / Auto Increment column
FK	projectID	Int		Project ID
FK	evalID	Int	Y	Evaluation ID (Not yet implemented)
	groupName	Varchar(50)	Y	Group Name
	isActive	boolean		Active/Inactive Project, default: true

Projects

Key	Name	Data Type	Nullable	Description / Attributes
PK	projectID	Int		Identity / Auto Increment column
FK	CourseID	Int		Course ID
	projectName	Varchar(50)	Y	Name of the Project
	description	Varchar(MAX)	Y	Description of the Project
	isActive	boolean		Active/Inactive Project, default: true

Users

Key	Name	Data Type	Nullable	Description / Attributes
PK	userID	Int		Identity / Auto Increment column
	username	Varchar(50)		Name of the User Account
	password	Varchar(255)		Users' Password
	firstName	Varchar(50)	Y	Users' First Name
	lastName	Varchar(50)	Y	Users' Last Name
	type	Char		User Account Type: U(user), A(admin), I(instructor)
	isActive	boolean		Active/Inactive Project, default: true

TimeCards

Key	Name	Data Type	Nullable	Description / Attributes
PK	timeslotID	Int		Identity / Auto Increment column
FK	userID	Int		User ID
FK	groupID	Int		Group ID
	timeIn	Datetime		Time In,Timestamp with timezone
	timeOut	Datetime?	Y	Optional Time Out, Timestamp with timezone
	description	Varchar(MAX)		Description of work
	createdOn	Datetime		Time card was created, Timestamp with timezeon

UserCourses

Key	Name	Data Type	Nullable	Description / Attributes
FK	userID	Int		User ID
FK	courseID	Int		Course ID
	isActive	boolean		Active/Inactive Project, default: true

UserGroups

Key	Name	Data Type	Nullable	Description / Attributes
FK	userID	Int		User ID
FK	groupID	Int		Group ID

---

Projected TimeTracker ERD