I am currently designing a database for students. I am stuck with deciding whether to make a many to many table for waiting students and a one to many for accepted and x-students.
This is the scenario:
- A student belongs to a family. So there is a tbl_student and tbl_family table.
- An ACCEPTED student only attends ONE class. Here I can just put the "classid" in the student table
- Here is where it gets complicated. A student can be waiting for many classes. When a place is free in the class, the student is ACCEPTED in ONE class. Do I make a seperate table for waiting students with a many to many broken up? OR do I make one many to many broken up for waiting students and keep the classid for accpted students in the student table?
- A student overtime becomes an x-student. We need to keep the record in case they miss a term and join again. Here if I have a many to many broken with accepted and waiting students, I will need to take the classid out for x-students, which means that a query like this will no longer work:
Code: Select all
select student_name from tbl_student,tbl_class,tbl_student_class where tbl_student.studentid = tbl_student_class.studentid and tbl_class.classid = tbl_student_class.classidtbl_family
familyid, int, primary key
family_name, varchar(50)
family_address, varchar(255)
tbl_student
studentid int, primary key
studentno, varchar(50)
student_name, varchar(50)
status, int (1 = accepted, 2 = waiting, 3= xstudent)
tbl_class
classid, int, primary key
classcode, varchar(50)
classstart, date
classend, date
tbl_student_class
scid, int, primary key
studentid int
classid, int
Any ideas will be appreciated and any questions answered. Please help