Hi,
I am currently working on a hold system. The system will allow
Coordinators to hold international students to place them with American
Host Families. I am having a hard time coming up with a good MySQL
table design for this process.
I have a table now that looks something like:
CREATE TABLE `hold_tracker` (
`id` int(11) NOT NULL auto_increment,
`STUDENT_ID` int(11) NOT NULL default '0',
`USER_ID` int(11) NOT NULL default '0',
`valid` char(1) NOT NULL default '1',
`date_held` bigint(20) NOT NULL default '0',
`date_created` bigint(20) NOT NULL default '0',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=12 ;
This works okay, for just keep track of who is holding the student.
There are some restrictions:
1. A Coordinator may only hold 3 students at a time. (PHP logic, this
is done already.)
2. A Coordinator can only hold a student while in the #1 position for a
certain amount of time. (I don't have a problem with calculating the
time.The problems are:
A. What do I do with the record once there hold has expired?
B. Also what do I do with the other records that are holding so
they get adequate hold times in the number one position?)
3. There can only be 3 holds per student. (PHP logic, this is done
already)
I can come up with some solutions for Restriction #2, but I feel that
they are kind of sloppy with the current table schema. I also think
that a better table schema would be in order here.
<sloppy_way>
After time has expired or the Coordinator has canceled the hold. I can
turn the 'valid' field of the record to zero. And update the next
Coordinators 'date_held' to the current date. Giving them adequate time
to have there hold.
</sloppy_way>
Has anyone else built a hold system before that might be able to help me
out with some pointers?
I hope this makes sense. If not please ask for clarification. As always
I appreciate any assistance.
Thank you,
Justin Palmer
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
