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 (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.
. . 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
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.