[RESOLVED] How best to store schedule

Derokorian

Hi all, I've finally been given approval to create a new timesheet system - which will replace our 15year old access database. I'm creating the tables now, and one of the things we need is a default schedule so to speak. This just needs to be the day of the week and the number of hours that day.

Say for example I work Monday 9-5 and Wednesday 9-3 and someone else works 9-5 M-Th. Since this sounds like a classic one to many relationship I thought it should go in its own table. Currently my table definition that I'm planning to use is:

CREATE TABLE `Emp_Schedule` (
  -- Primary Key: id
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,

  -- Foreign Key references Employees.id
  `emp_id` INT UNSIGNED NOT NULL,

  -- Schedule info, by day
  `weekday` ENUM('Monday','Teusday','Wednesday','Thursday','Friday'),
  `hours` DECIMAL(2,2),

  -- Indexes
  PRIMARY KEY(`id`),
  INDEX `emp_id` (`emp_id`),
    FOREIGN KEY (`emp_id`) REFERENCES `Employees`(`id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) Engine=InnoDB;

I was wondering if you guys thought ENUM was the best way to store the day. This is a set schedule so it doesn't need to be unique to a specific day in time, but rather the day of the week. I thought about using varchar, but with only 5 possibilities (Office is closed on the weekend, so no scheduled workers) that didn't seem like the best choice.

Bonesnap

Sorry, the spelling Nazi in me couldn't resist: you've misspelled "Tuesday".

And just curious about the weekends being closed: is there no possibility for overtime or unscheduled work, or is that beyond the scope of this?

Other than that I think ENUM is fine.

bradgrafelman

Bonesnap;11000272 wrote:
And just curious about the weekends being closed: is there no possibility for overtime or unscheduled work, or is that beyond the scope of this?

I shared this same concern when I first read this thread. I would include the weekends just to cover any future needs that may arise.

Honestly, if I were left to my own devices, I would probably have used a TINYINT column and used things like MySQL's DAYOFWEEK() function and whatnot to convert weekdays into integers. But that's probably just my evil side wanting to try and obfuscate little things like that; the ENUM is just as efficient and yet more descriptive (a.k.a. human friendly).

laserlight

This is probably more information than you need, but I recall reading a book on temporal databases. It was probably this one: Developing Time-Oriented Database Applications in SQL. Professor Snodgrass provides a free PDF version for download at his website; maybe you'll find that useful for the bigger picture of your project.

Derokorian

Bonesnap;11000272 wrote:
Sorry, the spelling Nazi in me couldn't resist: you've misspelled "Tuesday".

Yes I saw that in my script after I finished writing the other create tables, by then the edit button was gone. I assure you in my actual create table it is spelled correctly.

Bonesnap;11000272 wrote:
And just curious about the weekends being closed: is there no possibility for overtime or unscheduled work, or is that beyond the scope of this?

There is no scheduled work on weekends due to the way the policies are written. However one can have hours worked on the weekend, but this would go under a different table (Emp_Worked).

bradgrafelman;11000275 wrote:
I shared this same concern when I first read this thread. I would include the weekends just to cover any future needs that may arise.

True enough, I will add the possibility for weekend schedules just in case policy ever changes in the future.

bradgrafelman;11000275 wrote:
Honestly, if I were left to my own devices, I would probably have used a TINYINT column and used things like MySQL's DAYOFWEEK() function and whatnot to convert weekdays into integers. But that's probably just my evil side wanting to try and obfuscate little things like that; the ENUM is just as efficient and yet more descriptive (a.k.a. human friendly).

I do like the idea behind DAYOFWEEK however, I feel modification and such in the future will be easier for anyone responsible - if its blatantly obvious what the column is.

laserlight;11000280 wrote:
This is probably more information than you need, but I recall reading a book on temporal databases. It was probably this one: Developing Time-Oriented Database Applications in SQL. Professor Snodgrass provides a free PDF version for download at his website; maybe you'll find that useful for the bigger picture of your project.

I would have never found this on my own, thanks so much a cursory glance shows it to be exactly the type of information needed to develop this app!