Skip to content

Instantly share code, notes, and snippets.

@dragongling
Created November 18, 2021 14:31
Show Gist options
  • Save dragongling/4fa8671968109feb0bf0d24b4adcbf52 to your computer and use it in GitHub Desktop.
Save dragongling/4fa8671968109feb0bf0d24b4adcbf52 to your computer and use it in GitHub Desktop.
WeeklyReport schema
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)
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment