List:General Discussion« Previous MessageNext Message »
From:Justin Palmer Date:July 14 2004 7:37pm
Subject:Hold System
View as plain text  

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`)

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.  (Program 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. (Program logic, this is done

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.

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

Hold SystemJustin Palmer14 Jul
Re: Hold SystemSGreen14 Jul