Interview scheduleing in a database

hardburn has asked for the wisdom of the Perl Monks concerning the following question:

I'm currently rewriting an interview scheduleing CGI as part of my company's placement service. The orginal scripts kept information in a flattext database, which I'm now moving into MySQL. Interviews are scheduled during my company's annual meetings, so there are a limited number of time periods when interviews actually take place.

Each (potential) employee and employeer submit the times they are available for an interview. In the orginal scripts, these were stored like this:

Tuesday, 8:00 am
Tuesday, 8:30 am
Tuesday, 9:00 am
. . . 
Tuesday, 4:30 pm
[download]

For each time period, a '1' is stored if the employee/employeer is available at that time, or '0' if they aren't. Our own staff then matches up a time when a prospective employee or employeer can do an interview, at which point that time period is marked with who they are interviewing with. To me, this seems like a lot of wasted space.

Is there another way of structuring the database? Any replacment structure should have the same time scale (every half hour, from 8:00 AM to 4:30 PM, Tuesday and Wednsday) and be able to mark times as "available", "unavailable", or "scheduled". If "scheduled", it needs to point to who they are scheduled with.

Comment on Interview scheduleing in a database Download Code

Replies are listed 'Best First'.
Re: Interview scheduleing in a database by FamousLongAgo (Friar) on Nov 11, 2002 at 16:55 UTC
This isn't really a Perl question, but there's plenty of database people here... One thought is to use a table like this, where e_id is the employee id, i_id is the interviewee id ( assuming separate tables for employees and interviewees, with a primary key ), and the time period is a reference to a time slice. Let's say further that if an employee is unavailable, you schedule a 'dummy' interview with a special interviewee called 'Unavailable' ( id 0 below ). This lets you avoid having to have entries for every possible time slice. The assumption is that employees are available more often than not - otherwise, change 'Unavailable' to 'Available' and test accordingly. `e_id i_id time_period ------------------------- 122 204 4 104 0 6 122 114 1 194 0 2 194 0 3` [download] In this example, employee 122 has interviews in time segments 1 and 4 ( perhaps 8:30-9:00 and 10:00 - 10:30 ), and employee 194 is unavailable from 9:00 to 10:00. Does that help?	[reply] [d/l]
Re: Re: Interview scheduleing in a database by hardburn (Abbot) on Nov 12, 2002 at 14:54 UTC
Hrm, intresting. This certainly helps space efficency, but is probably more difficult to code. sigh we just can't win. Thanks!	[reply]
Re: Re: Re: Interview scheduleing in a database by jreades (Friar) on Nov 12, 2002 at 15:13 UTC
The other awy to do it is to use a combination bitmap/tied hash. This way would have a number of distinct drawbacks, but would save you having to use a database (which, IMO, is the better way). You could build a bitmap for each employee/employer, and then AND them together to see what's left -- what's left, of course, are the available times for interviews. Then, assuming that you get a match, you could tie() to a hash and create a reference in (for instance) the appropriate employer's timeslot that points to the person being interviewed (or you could just enter their name, but I like the idea of a hashref) and use it to print out reports and so forth. Now, suddenly, the database method looks a little bit better, no? ;)	[reply]


Your skill will accomplish what the force of many cannot
	PerlMonks