I don't see how hold_tracker coordinates a student with a coordinator (is
the coordinator the "user_Id" ?)

What is this "#1 position" you speak of? I don't see anything like a
"priority" or "ranking" field in the table you posted. Maybe if you posted
your entire data structure, things would make better sense.

It sounds like your system equates to a reservation system for a resource
limited to 3 concurrent users with each user being limited to consuming
only 3 resources at a time.  (Equating this to your model,  your
coordinators would be the users and the students would be the resources).
You ask what should happen to the "expired" holds.... I guess that depends
on if you need to keep that information (audit log, reservation history,
...) or not.

I would expect that if a coordinator has two holds then he would have two
records in the "holds" table (hold_tracker?). If a hold is expired you can
either copy it to another table (to archive it) or delete it.

I mentally model the system like this:

CREATE TABLE Student(
ID int not null auto_increment primary key,
FirstName varchar(30) not null,
LastName varchar(30) not null
... other student fields ...
)

CREATE TABLE Coordinator(
ID int not null auto_increment primary key,
FirstName varchar(30) not null,
LastName varchar(30) not null
... other coordinator fields ...
)

CREATE TABLE Hold(
ID int not null auto_increment,
Student_ID int not null,
Coordinator_ID int not null,
DateCreated datetime,
Primary Key(Student_ID, Coordinator_ID)
Key(ID),
Key(Coordinator_ID)
)


I defined the primary key that way so that each Student could be assigned
to any Coordinator only once. I had to add an index the ID field because
auto_increment requires it. I added an index for the Coordinator_ID so that
you could join Hold to either table rather quickly.

To determine how many Holds a Student participates in:
SELECT count(1) as numholds
FROM Hold
WHERE Student_ID = <some number>

Or if you want to go by name:
SELECT count(1) as numholds
FROM Hold h
INNER JOIN Student s
ON s.ID=h.Student_ID
AND s.FirstName = 'Joe'
AND s.LastName = 'Brown'

Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


                                                                                       
                                
                      "Justin Palmer"                                                  
                                
                      <[EMAIL PROTECTED]        To:       <[EMAIL PROTECTED]>          
                             
                      g>                       cc:                                     
                                
                                               Fax to:                                 
                                
                      07/14/2004 03:37         Subject:  Hold System                   
                                
                      PM                                                               
                                
                      Please respond to                                                
                                
                      justin                                                           
                                
                                                                                       
                                
                                                                                       
                                




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.  (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
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



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to