CODEWITHSUNDEEP
phpmysqldatabase-design
ILikeTacos
ILikeTacos

Reputation: 18676

Improving my current database design to store employees' schedules

I need to come up with a software able to store employees' schedules in a database. I currently have this design:

 CREATE TABLE IF NOT EXISTS `schedules` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `employee_id` varchar(32) NOT NULL,
   `day_of_week` int(2) NOT NULL,
   `starting_time` time DEFAULT NULL,
   `ending_time` time DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

Pretty straight forward. Print today's date, figure out what is the day of the week, and retrieve all matches from database, but I'd like to know a better way to achieve the same thing.

I need to display on a calendar employees' schedules and I need to pass a date to the calendar in order to be displayed. However, I cannot pass a date to the calendar if the only information I know is that a given employee is going to work on Wednesday.

Given the latter database design, is there a way to retrieve all the Wednesdays, or Mondays of a month/year?

Thank you!

EDIT

I need help going backwards (or a better alternative) to change day of the week to actual dates.

These are my "restrictions":

For instance: John Doe works Mondays, Wednesdays and Fridays. 8:00 - 12:00.

That information, with my current database design, is represented in the following fashion:

ID, employee_id, day_of_week, starting_time, ending_time
1, John Doe, 1, 8:00, 12:00
2, John Doe, 3, 8:00, 12:00
3, John Doe, 5, 8:00, 12:00

I need to be able to pass a date to the calendar UI for a given month.

For instance I should be able to come up with a way to tell my calendar control: John Doe is going to work on the 6th, 13th, 20th, and 27th taking in consideration that the only information I have is "day_of_week" = 1 (Mondays)

EDIT 2

My current but ugly solution is: Loop through all the days of the month, and query the database day by day.

EDIT 3 - SOLUTION

Thanks to RS, I was able to solve my problem. I kept the schedules table as it was, but I created the tables suggested by rs After creating the tables described on the article shared by rs The following query did the job:

 SELECT CONCAT(users.firstname,\" \",users.lastname) as employee_name, 
 DATE_FORMAT(DATE, '%a %b %d %Y') as date, schedules.starting_time,
 schedules.ending_time FROM dates_d 
 RIGHT JOIN schedules ON schedules.day_of_week = dates_d.day_of_week
 LEFT  JOIN users ON schedules.employee_id = users.ID

I JSON-Encoded an array, and the Calendar Control finally worked like a charm.

Upvotes: 1

Views: 2263

Answers (2)

rs.
rs.

Reputation: 27427

you can create date table and use key from that table and store here. The date table will allow you to query data by different date variables, ex: day, week, day of week, quarter etc - dwhworld.com/2010/08/date-dimension-sql-scripts-mysql

your schedules table will have id, emplid, date_id and you can join schedules with date table on schedules.date_id = datetble.date_id and get date, day of week in one query. You can then use this date field with your control

Upvotes: 3

Paul Aldred-Bann
Paul Aldred-Bann

Reputation: 6020

Why not have your day_of_week column a date type instead, that way if you want to get the day from this date you can use MySQL's DayOfWeek function. You could use this same date to pass to your calendar control (I think that's what you were getting at) in your UI.

To elaborate on your comment: Wouldn't the date that John Doe is scheduled to work be entered by the user (possibly via jQuery UI's datepicker), then when rendering your schedule at the UI level you'd use the following query to retrieve their monday schedule:

SELECT * 
FROM schedules 
WHERE DAYOFWEEK(schedule_date) = 1
AND employee_id = (
    SELECT employee_id
    FROM employees
    WHERE employee_name = 'John Doe'
)

Obviously this query makes some assumptions on your employees table, and it's not particularly elegant but it serves to explain my meaning.

RE: EDIT

OK, now I see what you mean. Basically you're after a function that will give you all dates between two ranges (perhaps) that land on a specified day (let's say monday). It seems this has already been done in PHP so that might be useful? Or do have a specific technology in mind that this would need to be done in?

EDIT 3

This seems a more elegant solution: Get mondays tuesdays etc - from this you can query your DayOfWeek in the SQL and return an array of integers that you can pass as the third argument to their function.

Upvotes: 1

Related Questions