学校管理MySQL数据库设计

Question

I'm designing a database for a school management system and am facing couple of problems and I thought of throwing it here and someone might help. I've a STUDENTS table that holds students details and CLASS table that holds class information. In the application one will need to know to which class a student should be promoted if he or she passes the exam. So the class table looks like this in my design:

+----------------------+
|id | name | parent_id |
+----------------------+

where the parent_id is the id of the previous class. Now each class has more than one stream (e.g., class form 1 can have 3 streams: form 1A, form 1B, form 2B etc) and each stream has students say form 1A has 40 students and so forth. So i have a stream table with this design:

+---------------------------------------------+
| id    | student_id | class_id | stream_name |
+---------------------------------------------+

so for each stream with 40 students I will have 40 rows in the stream table and the stream name will contain the name like A, B, C or whatever the user wants it to be named. Is this the best design regarding my problem? Will this design affect performance of the system in any way? What is the best database design approach with the problem in question?

STUDENTS table carries information about the student like their name and parents information.

EDIT: The stream table is updated every-time a students is registered or when the new academic year has been registered for-instance if i were in stream A of form 1 then all the student in that class (form 1) should be promoted to class form 1 retaining their stream so if i was in form 1A then the next academic year i will be in form 2A. There is an ACADEMIC_YEAR table that holds the information of academic year such as when does it start and end and all sort of information, also there is a EXAM_RESULTS table which stores results for each student in particular stream in an academic year. For records i will need to know all stream and classes that the student has studied.

Thanks in advance

Answer 1

Your approach is on the right track. I would like to recommend the following:

a) Add a start date and end date to the stream table so that you can calculate which period the student was in the stream (will be able to handle students who start mid way the year or who leave before the year ends). This can also handle students who repeat because they will be in the same class for a new year

b) In the exam results table, add stream_id, class_id, student_id fields so that you can associate the exam with a class, student and stream. There seems to be a duplication to have the stream_id in addition to student_id and class_id but from experience it speeds up queries when you do not have to join to the stream to find out which class and student the exams belong to.