Weekly employee availability database design

I am designing a scheduling system for many employees to work around the clock from home. When a new event is scheduled by a user (1 hour long event, starting on the hour, 24 hour choices),开发者_运维问答 the system needs to allow our users to select which employee they want to have work for them. I need to report who is available at that exact time. We have over 100 employees, and their availability changes frequently.

schedule table:

EventID EmployeeID

startTime (datetime)

endTime (datetime)

availability table:

EmployeeID

00:00

01:00

02:00

03:00

...

23:00

Records in the availability table are stored as a varchar(7). Length of 7 is for each day of the week. Never available at this timeslot is '0000000'. Always available at this timeslot is '1111111'. Available only on Tuesday at this timeslot is '0010000'.

EDIT: this information is for one week, hence the 7 char length. From this week and into infinity, this table design assumes that the employee will be available at the same time each week (unless they are working). Of course, the employee can change their availability, and then the table can reflect those changes from that point forward. /EDIT

Finding out what employees are available at any given time requires a lot of looping which obviously bogs down the system and is not good for anyone.

This is the best way I have come up with to implement this system, short of possibly having a table to store each employee's availability. I realize mine is a terrible method. Can someone please point me in the right direction?

I think you might be better off storing each users availability, it's not actually that much data, plus you can expiring it once it's passed.:

create table employee (
  `id` TINYINT(3) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT
);

create table `employee_availability` (
  `id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `employee_id` TINYINT(3) UNSIGNED NOT NULL, KEY `employee`(`employee_id`),
  `start_time` DATETIME, KEY `start`(`start_time`),
  `end_time`   DATETIME, KEY `end`(`end_time`)
);


SELECT e.* FROM employee
  JOIN employee_availability ea ON ea.employee_id = e.id
WHERE NOW() between start_time and end_time

Have a different table to store the employees' availability. I don't see why you shouldn't.

You should always avoid putting multiple data fields in one column (as your varchar column). I propose one row per employee, timeslot and day. That way you can speed up the queries a lot by indexing.

Even if you keep your ugly "everything-in-one-column approach" you should change to a CHAR because right now you're using up a byte per row just to keep the size of the column, even though it's always 7 chars long. Or even better, use a TINYINT (1 byte) and store the availability as binary flags. But of course, avoid this at all costs - you really do want to normalize your database.