idusty
asked on
What would be the best way to organize a db for employee working hours data?
Hi,
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?
Thanks.
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?
Thanks.
ASKER
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?
db.png
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?
db.png
ASKER
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... :-/
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... :-/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for both of you. The answers were exactly the type I was hoping for.
Thanks for the points - it's a great question, ~Ray
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!
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!
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?