Database schema for a school

Question

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 nvarchar(512),
MotherName nvarchar(512),
FatherMobilePhone nvarchar(64),
MotherMobilePhone nvarchar(64),
FatherProfession nvarchar(64),
MotherProfession nvarchar(64),
FatherPlaceOfWork nvarchar(256),
MotherPlaceOfWork nvarchar(256),
Observations nvarchar(3000)
)
create table StudentInstance
(
StudentInstanceId int primary key identity(1,1),
GradeInstanceId int foreign key references GradeInstance(GradeInstanceId),
StudentId int foreign key references Student(StudentId)
)
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
)

If you find an attribute that should be null checked, please disregard it. I've gone over this with the client and they want certain things to left blank if so chosen by the end user.

My main concern when designing this database was how to associate a student with a grade here and now, yet keep a record for previous years and manage to see what grade he got back in 2009. See?

I think I've done a good job but you never know - the hivemind here will probably find a flaw and I'd love some feedback.

Answer 1

Additional Observations:

1. Consider a new lookup table Phonetype (e.g. Mobile, Home, Work, etc.).
2. Create PersonPhone table that will have the PhoneTypeId, PersonId, and PersonType (student, faculty, parent, etc.) field and also a linking table between Phone and Person
3. With today's modern and extended families, a Father/Mother field will not suffice. Consider renaming to Guardian1/Guardian2.
4. Create a relationship list table (e.g. Mother, Father, Grandmother)
5. Create a StudentRelationLink table that can specify the student's legal guardians as well as other important people for the student (e.g. Emergency Contacts)
6. Is it necessary to have the last name of the father and mother on the Staff?
7. A Profession look-up might be beneficial on the student's parents
8. I assume this product will be web-based and multiple users could modify records simultaneously. You might want to add fields to each table to maintain concurrency. (e.g. Created, LastModified)
9. I don't see any tables that enforce permissions on your application. This might be okay for a small scope, but when your product grows and more people start to use, you may want to give them read-only access or deny them access all together to certain areas of the program

---

NOTE:

Creating school management software from the ground-up is a daunting task. That being said, you've got to start somewhere and, as long as your client is happy, you should see pay checks for many years to come :)

• Ant: Some great points, but I'd remove mother/father/guardians entirely and create a new "Guardians" table. What if a child has amicably divorced parents who have remarried and now are all considered responsible for the child? Guardian3 and Guardian4? What if siblings are at the same school? Will you repeat the data? Splitting it into a separate Guardians table and a join table is a more flexible solution.
• ray023: That makes more sense. Remove Guardians off of the Student table and consider using the StudentRelationLink table for marking a student's guardians. Also, in StudentRelationLink , you can add useful fields like CanReceiveMail, CanPickUpFromSchool, IsEmergencyContact, IsGuardian, etc.
• ray023: Also, while you're at it, you might to consider stripping out the address from student too and putting that in a separate table. Students from split families will have multiple addresses and you list one as the Primary Household. Also, as Ant mentioned, student's can have siblings who may (or may not) have the same address.

Answer 2

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 .

Answer 3

Here are some observations:

1. Beware of storing calculated information in the database, especially time-sensitive values like YearsOfService. You must ensure to have a (yearly?) process update this column based on the current date and DateOfHiring. What happens if that process fails? How do you catch it later? What happens if you restore from an old backup? Is it worth storing, or could you recalculate it when needed with little cost?
2. The Staff and Student tables are nearly identical. Could you benefit from a Person table to allow a more generic data-entry form? It may not be worth it since they would probably create a new record if a student were to become a staff member.
3. I find it much easier to analyze a schema with a couple rows of sample data per table, except for the obvious tables like Student. I assume Level is grade level (1st, 2nd, etc), but I'm not sure.
4. I think StudentInstance and StudentGradeReport should be merged. Each row tracks the scores for a student plus grade plus year, or in your case, student plus grade-instance.
5. GradeInstance is a joining table for Grade, Staff, and year. The use of the word "instance" here works somewhat but I think could be replaced with something more meaningful, maybe even GradeYear.
6. In the U.S. we call Level a grade, and Grade a class, at least that's what I get from your table definitions. Again, sample data would help. You might be missing an actual table for Year to which you can relate the Grades that are taught that year?

Answer 4

1. Staff - "YearsOfService" isnt necessary as it can be aggregated from the DateOfHiring attribute.
2. Staff - Is "PlaceOfBirth" even interesting to know? Would this field be useful as something more than just trivia?
3. Staff - "Sex" could preferably be changed into "Gender"

I think your Grade and GradeInstance is weird. A GradeInstance has a Staff and a Grade? And then your StudentInstance has a GradeInstance?

I also think that keeping track of First/Second/Third trimester in your StudentGradeReport table is bad because you would need to update every StudentGradeReport record as soon as this changes. This could possibly be done by keeping another table called Trimesters, which has first, second, third as columns, and a daterange as tuples and then aggregate from the current date

• Malachi: PlaceOfBirth is something that is used in background checks and stuff like that, so this is not just trivia. in a school I would want that school to have as much background on their teachers as possible
• Max: Didn't know that. And this is in the US? Not sure if we do it (probably to some extent)
• Malachi: probably more for a credit check type of thing...lol but that is what I would assume. and this Database layout has been approved by the Customer already so I imagine that is a field that the customer wants.

Answer 5

How about having more generic tables.

• PERSON with a field type_of_person where 1 = parent, 2 = parent (for divorced families), 3 = personnel, 4 = student etc that way you keep all the basic information in one table.

• Afterwards create a table with addressing information that will be connected with only adult records of the table "PERSON"

• Finally create a table with member specific data. If the member is of type = 1 or 2 (parent) maybe keep some personal information, like profession (he may be a doctor, a policeman or a fireman that you may need to call in case of emergency), special requirements,notes whatever. If it is type 3 = personnel (wages, timetables, tax info etc). If it is 4 = student you can store grades, year, special info,...whatever.

• Anirban Nag 'tintinmj': Don't put everything in one para. Use some bullets or points to break down your points and make it more readable.