# Database design for a school system

This system has to manage students, teachers, staff and grading. This is for production and is not a school assignment. As such, please let me know if I can improve on any aspect. :)

My main concern is automation. I'd like my software to be able to run regardless if I still exist.

I'm using SQLite as the database:

create table User
(
ID integer primary key autoincrement,
);

create table Area
(
ID integer primary key autoincrement,
Name string
);

create table Subject
(
ID integer primary key autoincrement,
Name string,
Abbreviation string,
IDArea integer references Area(ID)
);

create table Level
(
ID integer primary key autoincrement,
Name string,
Principle string
);

(
ID integer primary key autoincrement,
Name string,
IDLevel integer references Level(ID),
Observation string
);

create table StaffType
(
ID integer primary key autoincrement,
Name string
);

create table Staff
(
ID integer primary key autoincrement,
IDStaffType integer references StaffType(ID),
Name string,
LastNameFather string,
LastNameMother string,
DateOfBirth string,
PlaceOfBirth string,
Sex string,
Carnet string,
Telephone string,
MobilePhone string,
FatherName string,
MotherName string,
FatherContact string,
MotherContact string,
FatherPlaceOfWork string,
MotherPlaceOfWork string,
DateOfHiring string,
YearsOfService string,
Formation string,
Specialty string,
Category string,
Salary string
);

(
ID integer primary key autoincrement,
IDStaff integer references Staff(ID),
Name string
);

create table Student
(
ID integer primary key autoincrement,
Rude string,
Name string,
LastNameFather string,
LastNameMother string,
DateOfBirth string,
PlaceOfBirth string,
Sex string,
Carnet string,
Telephone string,
MobilePhone string,
FatherName string,
MotherName string,
FatherMobilePhone string,
MotherMobilePhone string,
FatherProfession string,
MotherProfession string,
FatherPlaceOfWork string,
MotherPlaceOfWork string,
Observations string
);

create table Attendance
(
ID integer primary key autoincrement,
IDStudent integer references Student(ID),
Attended string,
Date string
);

(
ID integer primary key autoincrement,
IDSubject integer references Subject(ID)
);

create table ScoreRecord
(
ID integer primary key autoincrement,
IDSubject integer references Subject(ID),
IDStudent integer references Student(ID),
FirstTrimester integer,
SecondTrimester integer,
ThirdTrimester integer,
Year string
);


Any glaring room for improvement?

• Are you sure sqlite is what you want to use? What do you expect the number of concurrent users to be (reading & writing)? What are expected # of rows in each table? How will you handle audit trail, delete/undelete, edit/undo? Don't store passwords as plain text. Any idea up front what kinds of reports you'll be running? It's pretty clear for staff & students you will need to adjust the fields from time to time; it may be worth it to come up with a general mechanism for extensibility there. – Ron Jan 29 '11 at 22:09
• Concurrent users: 2. Each table will have at MAXIMUM 5 million rows, and that's REALLY stretching it. – Sergio Tapia Jan 30 '11 at 0:57
• I would suggest to set all columns which should not contain NULL values to "non null". – Tino Jan 30 '11 at 1:24
• I don't have time for a full review right now, but a quick note besides those I've seen below: I feel "Observation" as seen in various tables may benefit from being stored in a separate table for each (ie: StudentObservations) with timestamps and perhaps user auditing. In general I would think User roles (for who/what they can and can't change) would be pretty important for a school too, though this sounds like a pretty small setup. – TheXenocide Feb 2 '11 at 23:55

One thing which immediately jumped out to me is this:

LastNameFather string,
LastNameMother string,
FatherName string,
MotherName string,
FatherContact string,
MotherContact string,
FatherPlaceOfWork string,
MotherPlaceOfWork string,


Your design assumes that every student will have exactly 1 mother and exactly 1 father. I can tell you now that will not be the case. Students with divorced parents may have two mothers and two fathers, all of whom will want to have their contact info listed. Some students may have gay or lesbian parents, and thus two fathers or two mothers. Some students may have neither, and instead may have a legal guardian who is neither their father nor mother.

One solution to this would be to have a table for a "person", and link people to each student. Identify whether that person is a father or mother (or non-parental guardian). This will also simplify having siblings: you can have the same mother, father, etc. for multiple students.

For the majority of students, this won't be an issue, but for more than you might think, it will. Do those families and your management a favor by making it easy to handle various family scenarios!

• I'll keep that in mind for the next iteration. I'll create a table "Guardian", with IDStudent, IDGuardianType(parent, guardian, etc.) and Name, Contact Info, etc. – Sergio Tapia Jan 30 '11 at 14:49
• Do keep in mind the possibility that a child will have two mothers or two fathers on their birth certificate. – doppelgreener Jan 30 '11 at 17:11

Some things that jumped out

take martin york's suggestion and be consistent as much as possible, user identifying primary key names like user_id as opposed to id for every table as it will make it less confusing for those writing/maintaining the SQL. For foreign keys, I would suggest table first e.g. area_id as opposed to IdArea as it flows better with natural language.

• Looking at your ERD, there are places where it is failing to get to 3NF (which is a level of database normalization you should strive for when possible)

• Use proper field types, looks like you are using string a lot, things like date of hire should be actual timestamp/datetime column, gender can be enum, etc.

• All the contact/phone information in the staff and student tables can be places in more appropriate phone/contact tables as those are one to many relationships.

• Your attendance table...looks like it either states that a student attended school a particular day or not...seems like this would be something more on the per class level, as a student could attend a half day and such.

• Is it possible for a staff member to have more than one staff type, if so you should have an associative table to link staff to staff types

• Instead of years of service in staff, could you not just use the date of hire column to figure that out instead of every year updating the year of service column.

Those are some things I noticed, hope it helps.

1. All your ID (unique ID's) are called ID (this is OK and it works for you)
But I have found when you start doing joins this may become hard to read. Thus I like to name the unique key after the table. eg User_ID on the User Table etc.

2. To make the Identifiers easier to read either use camel case or separate words with _
Identifiers like IDArea run together a bit much. So IdArea or ID_Area or Id_Area (Remember pick a style and be consistent though).

3. The Staff table has a lot of information in it a lot of which could be NULL.
I would rather have a Person table to hold people information. Then a relationship table to hold relations ships between people. This way when your DB is expanded (and in a Business environment this will happen) you can express other relationships between staff.

4. Related to Staff. Like Staff I would keep the personal details of Student in the Person table. Note. I would still keep a seprate table (or view) for Staff/Student that has a link into the Person table.

• Regarding point #1, I use Entity Framework, so in my code I go: .FindAllStudents().Where(s=>s.ID == parameterID); Like that. :P – Sergio Tapia Jan 29 '11 at 17:22
• Point #1 is a swings and roundabouts thing. If you always call your primary key ID then you know that ID is always your primary key - there's convention over configuration benefits that arise as a consequence to offset the potential SQL issues (though I'd suggest that tableID = table.ID is fairly clear) – Murph Jan 31 '11 at 18:21
• @Murph: This is a code review all points are subjective. And on simple queries then ID is fine. But once you start writing complex multiple nested queries (which is what happens in the real world) that extend past 2 three pages the extra clarity does actually bye you some benefit. – Martin York Jan 31 '11 at 19:02
• everything is a compromise, you give up one thing to gain another. And I've done complex queries (-: – Murph Jan 31 '11 at 19:25

I aggree with all of the other's observations above, but I will touch on a few particular points:

1. You could definitely do some additional normalization. There are places where this may come down to a concious decision NOT to fully normalize (there can be some reasons not to . . . Depending on your problem space), but overall, when I begin to see the same field names appearing in multiple tables (other than PK/FK relationships), I become suspicious. An example (which Martin York touches on, but I will take a step further) is all of the "People" fields. Note the level of dupplication between the "Staff" table and the "Student" table with regard to Mothers/Fathers/Worplaces/Professions. Mothers and fathers are people. Staff are also people. Students are people.

2. I notice you have an "Observations" field in your student table. I strongly recommend you define an "Observations" table with a foreign key to the "Students" table with the following fields (The Staff_ID is to record who made the observation). Over time, observations will accumulate, and it will be helpful to know when, and who made them:

Observations Table: Observation_ID, Student_ID, Observation_Date, Observation, Staff_ID

1. I agree with the notion that there should be a separate "Contact_Info" table. Further, I am betting you will want to send the student (or the student's parents) mail from time-to-time. I would include an "Addresses" table for this purpose. I won't pretend to know how it works in Bolivia, but in the states, schools like to mail out report cards (although, parents ALSO can track their student's progess over the internet anymore . . .).

2. Staff salaries can change. While you may decide it is acceptable to overwrite this value, this is another area where correct normalization indicates a Staff-Salaries table, which includes fields for ID, Salary, and Effective_Date.

How far you take the normalization process can be tricky, but as one or more of the other commenters observed, you should always shoot for 3NF. I promise you, from my own painful experience, that when your boss decides he wants a report showing staff salary increases over a five-year period, you will be glad you did.

While it often happens that there is a concious, design-based decision to "de-normalize" a table, the decision should documented for those who may need to maintain your databse in the future, when you are no longer there.

Hope that helps!

I appreciate that all the names you choose for tables and fields are understandable and self-explanatory. Only "GradeParalelo" does not make sense to me.

Nevertheless, I would suggest adding short comments to describe fields and tables. Include the motivation for design decisions that required complex thinking, especially if you expect the system to be maintained by different people.

• It's because it's in Spanglish :P Basically, here in Bolivia you can have many 'instances' of a grade. And a student belongs to a single instance of a grade. – Sergio Tapia Jan 29 '11 at 18:07

There seems to be duplication, I'm just going to put changes for each table in a separate answer.

Staff Table

1. The mother and father contact information could be put into a separate table. That way you are not limited if someone has different contacts

contact_id, contact_name, relationship, contact_number, contact_address, fk=staff_id

2. Can a Staff have more than one StaffType? Like can a teacher be a coach? If so the table must be altered. Will all types have a specialty?

3. Regarding DateOfHiring and YearsOfService, the length of service can be deduced using the current date and the DateOfHiring.

4. I don't see a way to deactivate a staff member. What happens when they are terminated? Dropping their record is not a good solution. You may want to consider adding a termination date to the table.

All tables use automatically assigned sequences as keys, this is not proper data modelling. Many people do that and forget about the actual/business/real world Primary Keys. You don't want to allow multiple users/areas/subjects with the same name, thus you must add Unique Constraints on User.username, Area.name, Subject.name, Subject.Abbreviation, etc.

The ID in SubjectGrade is useless, it's never used/referenced, the logical key is IDGrade,IDSubject.

Similar for ScoreRecord: IDSubject, IDStudent instead of ID

## protected by Jamal♦Aug 19 '17 at 21:14

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).