School Database Schema

database-design

Im working on a simple school system which handles student registration and schedule. Furthermore, the system should handle different type of schools Such as Kindergarten, Primary, Secondary, and High (Pre-school and k12)..

Im not an expert in DB design but still follow what i can from what i learn through reading and practice.

  • Student
  • Parent
  • Student_parent (If there is more than one parents want to be in the system)
  • School (Where the system can check student belong to which level (Primary or Secondary ..etc))
  • Subject (All the subjects in the schools)
  • Class (Basically Schedule-timetable)
  • Classroom (Every room and lab in the school (Basically facilities of the school))
  • Attendance (Not yet)
  • Marks (Not Yet)

are the tables relationships sufficient or need to redesign it?
Any problem with this basic schema?
Is it sufficient for the system?
How to Implement terms (Semester 1 and semester 2) as well as Years?
What about when the year ends. How to transfer students to a new year (How to implement this)?

I hope to get some points on improvements or issues with the schema before start programming it.

Thank you
enter image description here

.
.
Edit: implementing John Herbert suggestions.
.
.

Implementing John points except of the last one because there are no departments in the school and the number of students really subjective to the year.

  • changed tables names with prefixes to group them.
  • Modified few field as suggested by John for better search and grouping
  • Added table term and connected it with school (KG- Primary- Sec..etc)
  • Modified field sizes from Int(11) to something smaller as needed

DB Schema After Editing

enter image description here

after implementing these things someone cant help but wander. Will i need to add indexes for performance in the future?
Where will indexes possibly be needed ?

I hope this can benefit someone who is interested in DB design.

Best Answer

All in all I think your Key Relationships are straightfoward enough and clear enough that I could easily find my way. You'll probably find a few places to change as you mock-up and eventually code your stored procedures and other processes. Here are my thoughts as I looked through it.

  • Replace parent with something more generic, like guardian. This use of language opens the door for other authority figures for the student, such as grandparents and adopted parents, as well as allow for a flag that indicates the person is an emergency contact or is allowed to pick the child up.
  • Separate the field [Full_Name] into two fields; [Given_Name] and [Surname]. This helps when sorting by name during programming. (It's better to have the two Williams sisters show up next to each other on a report than all the Mary's of the school grouped together).
  • Suggest moving the [term] field under the [subject] table into the [class] table. This field can be used to indicate if Bobby Tables was in that class during the Spring of 2010 or the Fall of 2009, with that specific teacher in a specific room. I would have another table that had the date ranges for those terms. (As a side note, INT(11) may be a little overkill for it. A hundred years of one-week terms would only really result in 5000 terms, with would only need INT(4))
  • On the subject of promoting students to the next year, it could be done with a new field under the [student] table, with a simple INT(2) to indicate the year of their education. A stored procedure that is run at the end of the school year could check their credits earned and move them up.
  • A consideration can be made also to include in the [subject] and [teacher] tables would be a department, such as Math, Science, or English. This could be used to help group together all the Science teachers, or show all the History classes that are available that term, especially if the school is large enough to have more than one session of a particular class.
Related Question