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]
