It's really hard to give you an answer that sufficiently solves your question. The reality is that the point of using a relational database is to model real world relationships between entities.
A school database can be quite complex. It has to take into account a lot more than just a few courses, students, etc. And normalizing that data (removing redundancy) is complex!
For example, heres what some of those relationships might look like:
# (middle initial is last for optimization)
+- primary key, unique, not null
+- [foreign key] => position_id
+- enum (Male/Female)
+- not null
+- can be a date, a string, etc
+- not null
+- [foreign key] => address_id
+- not null
+- not null
+- can be null
# the position table tells you the type of person
# everytime a new position is created, it's added here
# this can be simple w/ 2 records:
# 1. student
# 2. faculty
# but it can also have administrative staff, etc and grow
# rather large
+- primary key, not null, unique
# holds all addresses.. these can be shared by faculty and students...
+- can be a hashsum, an int, etc... but it can also be
a composite key, made up of every other field in this table
+- [foreign key] => municipality_id
+- [foreign key] => county_id
+- [foreign key] => country_id
...contains attributes of all municipalites that
the school serves. new ones can be added, old ones
can be removed...
...same as municipality
# list of departments
...etc, more attributes of a department
# list of available courses
+- [foreign key] => department_id
# current semester and all previous semesters
...this is where the semesters are stored
- foreign key for course_id
- foreign key for semester_id
- foreign key for student person record
- foreign key for teacher person record
Now, this isn't perfect by any means. It's rather incomplete! Providing you with a fully normalized, air-tight database schema is beyond the scope of this post (unless you're willing to hire a contractor, wink).
The point I was trying to make with the schema outline I provided is that you want to store entities in unique tables and link those tables together to show relationships.
person table is a good example because that table can have quite a few relationships. For example, more than one person may live at the same address and so we've added an
address table. We have to describe the
role of that person -- are they a student? faculty? administrative employee? volunteer? school board? emergency contact? A grandparent who is authorized to pick a child up? etc, etc...
Also, one could argue that
gender is shared across multiple records and should be a foreign key instead -- that would be a poor decision. Since there is such a small number of possible answers, it's more efficient to use a databases built-in
enum type. The database engine will optimize
enums for you.
If you don't separate entities your database performance will suffer. Imagine if you had the same counties and municipalities duplicated thousands of times in the address table! Municipalities and counties are finite -- usually a school can limit them down to a certain geographical area, say within 100 miles of the school.
This way, in the future it is easy to add new counties, if a new person has an address that resides in a municipality or county that doesn't have a corresponding record.
You may also want to create a country table if you have even the most trivial reason to believe that students who permanently reside in another country will be in the database. Think foreign exchange student, or a student studying abroad.
As you can see, these relationships are complex and you need to think out your database accordingly.
For instance, there are quite a large number of entities that will be represented in the database. I'll list some below and let you take it from here:
- Keep in mind students may take the same class twice if they fail
There are more things you must keep in mind as well, which can be in the databse:
- Keeping track of vendors utilized by the school
- Receiving tuition, billing tuition, etc
- Keeping track of who has scholarships
- Keeping track of which students are on which sports teams
- Keeping track of GPAs and putting failing students on academic probation
- Keeping track of infractions, detentions, suspensions
- Keeping track of transfer students, etc