Database design for a school system

Question

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,
Username string,
Password string
);

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
);

create table Grade
(
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,
Address 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
);

create table GradeParalelo
(
ID integer primary key autoincrement,
IDGrade integer references Grade(ID),
IDStaff integer references Staff(ID),
Name string
);

create table Student
(
ID integer primary key autoincrement,
IDGradeParalelo integer references GradeParalelo(ID),
Rude string,
Name string,
LastNameFather string,
LastNameMother string,
DateOfBirth string,
PlaceOfBirth string,
Sex string,
Carnet string,
Telephone string,
MobilePhone string,
Address 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
);

create table SubjectGrade
(
ID integer primary key autoincrement,
IDGrade integer references Grade(ID),
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,
FinalGrade integer,
Year string
);

Any glaring room for improvement?

Answer 1

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!

Answer 2

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.

Answer 3

Comments

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.

Answer 4

I agree 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 conscious 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 duplication between the "Staff" table and the "Student" table with regard to Mothers/Fathers/Workplaces/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

3. 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 United States, schools like to mail out report cards (although, parents ALSO can track their student's progress over the internet these days ...).

4. 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 conscious, design-based decision to "de-normalize" a table, the decision should documented for those who may need to maintain your database in the future, when you are no longer there.

Hope that helps!

Answer 5

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.

Answer 6

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.

Answer 7

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