i was designing a class registration system with the following relationship:
student <--one to many--> registration <-- many to one --> class
while i was creating the db tables, i came across this field which i'm not sure where it should belong. there is what you call students on hold or students in the waiting list. there are certain reasons why this happen. one is that the maximum number of students allowed for a class is reached so subsequent registrations cannot anymore be accommodated. thus those registrations are said to be in the waiting list.
i was thinking. should i just add a column in the registration table to indicate that the a registration record is in waiting list, e.i:
registration_id fk_student_id fk_class_id is_in_waiting_list
1 3 2 N
2 1 1 N
3 2 2 Y
now i say record of registration_id 3 is an unqualified entry of the registration table 'coz it cannot fully utilize of the relationship the table has. take for example, registrations is associated with the payments table:
registration <--many to many--> payment
and say that a registration can be associated to any number of payment (this is used for paying in installment). now there is a constraint that waiting list entries cannot be associated to any payment.
Creating a separate table:
now, i was having problems. how can i insure that this policy is enforced. i mean, i don't think the db design is good enough to enforce this rule. another implementation possible is to create a separate table for the waiting list entries, thus:
student <-- one to many --> waiting_list <-- many to one --> class
student <-- one to many --> registration <-- many to one --> class
but my problem is it would be too ackward to work with two tables. suppose i want to count the number of registration regardless whether it was wait listed or not. then i have to query two tables.
another problem, is that when a waiting list registration is approved. it should be moved to the registration table. well, that's good 'coz i could retain the entry in the waiting list table and remember that the registration was previously wait listed. but is the analysis right? that two tables should be maintained instead of adding an additional column to indicate a record is wait listed as discussed in the 'Column indicator' section.