database design help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
gurjit
Forum Contributor
Posts: 314
Joined: Thu May 15, 2003 11:53 am
Location: UK

database design help

Post by gurjit »

Hi,

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.classid
I think my tables would be as follows with the above scenarios:
tbl_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
jrd
Forum Commoner
Posts: 53
Joined: Tue Mar 14, 2006 1:30 am

Post by jrd »

I would rather make one many to many broken up for waiting students and keep the classid for accpted students in the student table.
Post Reply