What would be the best way to organize a db for employee working hours data?


I need to store employee working hour shifts in a database. What would be the best way to organize this information in a MySQL database? The shifts are going to be changing in day to day basis. I will be inserting the data using a calendar application, which gives me the starting hour and ending hour values for a given day. Should each day then be just another row in a table? ...and connection to the employee in question using a foreign id?

Would there be a better way to handle this?

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
We might need a little more information to answer you thoroughly.

Can shifts span days?  Possibly so, and if so, the start and end values should be DATETIME fields.

What is the data going to be used for?  What kind of queries will you use to extract information from the data base?

How many rows of data will you expect to have?  How many employees?
idustyAuthor Commented:
Thank you for your reply Ray.

This is a hairdresser company. At first there are only 3 employees. Though I'm sure there are never going to be more than 6, it would be nice to have the system to be expandable. The system I have been building up is a booking calendar system, where clients can book their hairdresser time online. I have to be able to store the working hours for each day for each employee, so I can limit the time for the clients from what to what time can they book the time. Each employee will probably have different working hours for different days. Problem isn't how can I insert this data but how should I organize it in the db table, or should I use multiple tables?

To build up the calendar, I will be extracting each individual employees day schedule from the database mainly using the booking information from the "reservation" table (see the picture) Next what I need to extract also is into what hours should I limit each individual employees work day. And that information should be extracted from "schedule" table which does not exist yet. The question is how should I organize it in the best way?

idustyAuthor Commented:
To further answer your question: "Can shifts span days? "
I believe it will be simpler to design the schedule manager so that each day is being set individually. So that even though someone may have the same schedule 4 days in a row, it would make 4 different rules for it. But then again here we go quite a bit away from the "good design", don't we... :-/
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Ray PaseurCommented:
Thanks, that is helpful information.  The calendar type you want is called a "resource scheduling" calendar.  The design pattern works like this.

Each hairdresser is a "resource" that can be scheduled when the resource is available.  The hours of availability are restricted by (1) scheduled non-work hours and (2) appointments that make the resource unavailable.  For example, the hairdresser works from 9am to 4pm with an hour break for lunch at 1pm to 2pm.  That means the resource is unavailable each day from midnight to 8:59:59, from 1:00pm to 1:59:59pm and from 4:00:00pm to midnight.  As a practical matter, the resource might be unavailable after 3:30pm, because each appointment has a minimum time of 30 minutes, so you could not schedule a 30 minute appointment starting at 3:50pm.  You might also want to account for days off, as well.

Now with those basic constraints, you can begin to envision your calendar table  There would probably be only one table necessary for the master calendar, with a side table of client information.  Each hairdresser would have a "resource id" and as the appointments are added they would be associated with the client table and the hairdresser.  The duration of the appointment would contribute to the time that the hairdresser becomes unavailable.  So an appointment for 1/2 hour at 10:00am would take one of the hairdresser resources out of availability from 10:00 to 10:29:59.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
1)  yes, you should have multiple tables (that is the correct relational database design)
     An Employee table, a Schedule Table(?)  and an EmployeeShift Table.  You might even want to consider a Customer table

    Employee Table
       <other employee fields - address, phone etc as needed>

    Schedule Table
       SchedStart   (DateTime)
       SchedEnd      (DateTime)

   EmployeeShift  (which employee works which shift trhis would allow multiple employees on the same shift, mutiple shifts per employee (even on any give day, and also allow separate shifts for each employee on the same day)

Do multiple employees work on a given 'shift' or are the shifts set on an employee by employee basis

     Mary and Ellen both work 9-12 while Samantha works 12-3

     Mary works 9-12, Ellen works 10 - 2 and Samantha works 11-4

(clearly these are only for example purposes, and do not represent real shifts - but the idea would be the same)
idustyAuthor Commented:
Thank you for both of you. The answers were exactly the type I was hoping for.
Ray PaseurCommented:
Thanks for the points - it's a great question, ~Ray
Scott PletcherSenior DBACommented:
You're still going to need an employee time table -- just because they're *scheduled* to work until a certain time doesn't mean they always will.

Do you have employees clock in and out?  If so, you'll need a clock in/out table.

Perhaps if you treat them as essentially salaried you can make do without such a table.

Also, the schedule table seems overly simplistic to me.  Different days might have different schedules.  I might work less/more hours on weekend days then week days.

Also, the employee shift table should have either an associated history/archive table to contain old schedules and/or you have to add an effective date to the shift table.

If an employee's shift changes on a given date, I would think you might need to have their current schedule and next week's schedule in the db at the same time and they would/could be different.

Summing up, take some more time and *seriously* determine what your real needs are.  A poor design will hurt in a lot of ways later.  It's sooooooo much easier to put in some design time on the front end than to be frustrated by limitations and errors later!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.