Need Some Advice

[bschultz]

I'm the scheduler for a group of baseball umpires.  We have 20 umpires and work over 200 games a year.  I currently schedule through a VERY high tech way of picking names out of a hat.

 

Once I schedule the games by hand, I then enter them into a database so that our umpires can see the schedule on our website.

 

I'd like to automate this process.

 

Here's what I'm thinking:

 

Database structure - - -

 

-1 table for list of umpires (includes columns for name, level of service (varsity, JV, junior high, etc.), how many games they can work (veteran umpires get more games than new umpires) and whether that umpire is an active member (can be scheduled)

-1 table for the list of games (pretty self explanatory)

-1 table for the umpire login info (used to log into the system)

-1 table for the vacation days (individual umpires can log in and add days that they CAN NOT work)

-1 table for schools that each umpire can't work (too far to travel, had issues with a coach, etc)

 

I can handle all of this...EXCEPT the automatic scheduling of the games...and the following:

 

I want to be able to schedule games individually, by week, month or by the season (flexible).  I also need to be able to manually schedule games...and to automatically schedule games that are added at a later date (I've scheduled the season...but we add 20 more games later).

 

I also need to ensure that any individual umpire doesn't work more than "x" number of games per team.  That number "can" be exceeded if need be, but it won't unless it has to be.

 

I also need to be able to notify umpires (by email) if I make a change to their schedule.

 

I'll start with the automatically scheduling of games.  I'm sure that there is a php class or function that will help with this...but I have no idea what that might be.

 

Also, am I missing something in the db structure?

 

Can you please pass along any ideas you may have that might help me?

 

Thanks much!

[dangerousprince]

Easy.

 

Just run a cron job on the server (I can sort you out with some cheap hosting if not), run a load of IF statements to meet your requirements and use rand() to select the ID of members/players/teams to be added to the game.

 

Hope that helps.

[possien]

This may sound real crazy but the Army has what they call a duty roster.  Google: DA Form 6 duty roster.  In simple terms it increments by 1 every event that a ref would not be in.  The highest number gets the next job. They then go to zero.  Vacation and other things can be handled the way you want, the military lets the numbers keep going (when you get back you more than likely have some duty).  This may give you a different perspective.