WeeklyReport schema

Create_WeeklyReport.sql

create database WeeklyReport
go

use WeeklyReport;

create table Companies(
	CompanyId int identity(1,1) primary key,
	[Name] nvarchar(50) not null,
	CreationDate datetime
);

create table TeamMembers(
	TeamMemberId int identity(1,1) primary key,
	FirstName nvarchar(20) not null,
	LastName nvarchar(20) not null,
	Title nvarchar(20) not null,
	Email nvarchar(50) not null,
	CompanyId int not null,
	constraint FK_History_TeamMember foreign key (CompanyId) references Companies(CompanyId)
);

create table ReportingTeamMembersToTeamMembers(
	ReportingTMId int not null,
	LeaderTMId int not null,
	constraint FK_ReportingTM foreign key (ReportingTMId) references TeamMembers(TeamMemberId),
	constraint FK_LeaderTM foreign key (LeaderTMId) references TeamMembers(TeamMemberId)
);

create table ReportGrades(
	ReportGradeId int identity(1,1) primary key,
	[Level] int not null,
	Commentary nvarchar(600)
);

create table WeeklyReports(
	ReportId int identity(1,1) primary key,
	AuthorId int not null,
	MoraleGradeId int not null,
	StressGradeId int not null,
	WorkloadGradeId int not null,
	HighThisWeek nvarchar(600) not null,
	LowThisWeek nvarchar(600) not null,
	AnythingElse nvarchar(600),
	constraint FK_Report_Author foreign key (AuthorId) references TeamMembers(TeamMemberId),
	constraint FK_Report_MoraleGrade foreign key (MoraleGradeId) references ReportGrades(ReportGradeId),
	constraint FK_Report_StressGrade foreign key (StressGradeId) references ReportGrades(ReportGradeId),
	constraint FK_Report_WorkloadGrade foreign key (WorkloadGradeId) references ReportGrades(ReportGradeId)
);