Database schema for a school

Here is the database query needed to create the database:

```
create table Area
(
AreaId int primary key identity(1,1),
Name nvarchar(64) not null
)

create table Level
(
LevelId int primary key identity(1,1),
Name nvarchar(32) not null,
Principle nvarchar(512) not null
)

create table Subject
(
SubjectId int primary key identity(1,1),
AreaId int foreign key references Area(AreaId),
LevelId int foreign key references Level(LevelId),
Name nvarchar(32) not null,
Abbreviation nvarchar(16)
)

create table StaffType
(
StaffTypeId int primary key identity(1,1),
Name nvarchar(64) not null
)

create table Staff
(
StaffId int primary key identity(1,1),
StaffTypeId int foreign key references StaffType(StaffTypeId),
Name nvarchar(128) not null,
LastNameFather nvarchar(256) not null,
LastNameMother nvarchar(256) not null,
DateOfBirth datetime,
PlaceOfBirth nvarchar(256),
Sex nvarchar(8) not null,
Carnet nvarchar(64),
Telephone nvarchar(64),
MobilePhone nvarchar(64),
Address nvarchar(256),
FatherName nvarchar(256),
MotherName nvarchar(256),
FatherContactNumber nvarchar(64),
MotherContactNumber nvarchar(64),
FatherPlaceOfWork nvarchar(64),
MotherPlaceOfWork nvarchar(64),
DateOfHiring datetime,
YearsOfService int,
Formation nvarchar(128)
)

create table Grade
(
GradeId int primary key identity(1,1),
Name nvarchar(32) not null,
LevelId int foreign key references Level(LevelId),
Observation nvarchar(256)
)

create table GradeInstance
(
GradeInstanceId int primary key identity(1,1),
StaffId int foreign key references Staff(StaffId),
GradeId int foreign key references Grade(GradeId),
Name nvarchar(32) not null,
Year datetime
)

create table Student
(
StudentId int primary key identity(1,1),
RUDE int,
Name nvarchar(64) not null,
LastNameFather nvarchar(256) not null,
LastNameMother nvarchar(256) not null,
DateOfBirth datetime not null,
PlaceOfBirth nvarchar(128),
Sex nvarchar(8),
Carnet nvarchar(32),
Telephone nvarchar(64),
MobilePhone nvarchar(64),
Address nvarchar(256),
FatherName n

Your data needs to be more

ATOMIC AND HOMOGENEOUS

This table

create table StudentGradeReport
(
    StudentGradeReportId int primary key identity(1,1),
    StudentInstanceId int foreign key references StudentInstance(StudentInstanceId),
    SubjectId int foreign key references Subject(SubjectId),
    FirstTrimester int,
    SecondTrimester int,
    ThirdTrimester int,
    FinalGrade int
)

doesn't look right to me.

I think that you want something more like this

CREATE TABLE StudentGradeReport
(
    TableID INT PRIMARY KEY IDENTITY(1,1),
    StudentInstanceID INT FOREIGN KEY REFERENCES, StudentInstance(StudentInstanceID),
    SubjectID INT FOREIGN KEY REFERENCES Subject(SubjectID)
    Term NVARCH(2), -- this will tell us T1, T2, T3 or F
    SchoolYear INT,
    UpdateDate DATETIME,
    UserChangeID INT NOT NULL
)
-- the UserChangeID would be a foreign key to the Staff table(or User Table), 
-- assuming they are the only people that can change these things. 
-- this way you can keep track of who is changing things.

This seems like the right way to create this table.

You want your data to be atomic in all of your tables, but not redundant.

In your version of this table you had redundant data, you would fill in FirstTrimester with a 1 (true) and then the other 3 with a 0 (false) that is very redundant.

The data that would go into that table would have to be updated or overwritten if they wanted to change something or there was a mistake. This isn't good for keeping track of changes. In the table that I gave you have the ability to add a new row of data and then keeping track of who made the change, this way you can keep track of who changed what and when.

Having your data in the table that I provided it is going to be a whole lot easier to pull reports from. you can pull data from

• a specific student

• a specific subject

• a Specific school year

• a range of school years

• all changes made by a specific user

I hope that looking at my answer helps you to see how the rest of your tables need to be more ATOMIC.

StackExchange Code Review Q#1528, answer score: 9