February 14, 2004 at 8:25 pm
OK, I'm not sure where to put this question but here goes... I need to track all our employees time off:vacation, sick days, excused absences etc. I can pull the data out of our time keeping system and store it in a separate dB for eventual web posting for our intranet site. I plan to be able to go back one year from the current date. I thought of storing each recorded day as something like a julian data type character string(i.e. 1358V = Christmas Eve year 2001 was a vacation day) My plan is to color code a monthly calendar I found on-line and plan to modify (http://www.dynamicdrive.com/dynamicindex7/basiccalendar.htm) I can feed the function which creates each month with a parameter containing the list of days to color code in the calendar. I'm having a hard time thinking of a single dB table schema to store the list of past time-off days for each employee in. Any suggestions?
February 17, 2004 at 2:54 am
At what level are you looking to store off time - days, fractions of days or hours. It sounds like you need to track days employees were off work which could also be half days.
You also need to decide what side of the equation you will be tracking i.e. will you track time at work or time off work.
A time keeping system generally tracks time spent at work, so if you wanted to store time off work this would be an extrapolation of the data stored on the time keeping system.
Basically, what I am saying is that you should get your system design going before delving into the detail of the table design. Data layout logically follows the design phase of a project.
Not sure that helped, but the possiblities in terms of table design are endless within the frame of reference stated in your original post.
Stephen Marais
Integration Architect
Digiata Technologies
www.digiata.com
February 17, 2004 at 10:31 pm
Thanks for your thoughts. I may be going about this backwards as I am designing the web page I want to see first and then working backward to the dB data I plan to pull out. So far, I have determined that I will need a table or view or stored procedure result set showing each employees time-off for an entire month. The table will show: employeeID, month, year, and a string containing each day off. I am not going to separate the day into hours as my calendar shows only the day. The string will look like 1-V, 4-V, 6-S, 7-S, 22-V etc... where the string says the employee took off the 1st, 4th, 7th, and 22nd as vacation days and was sick on the 6th of that particular month and year depicted by the row in the table containing the employee's ID, month, and year. In the calendar function buildCal() I found above I will input the month, year, and the string of days off as parameters. I will parse the string of days off into a javascript two-dimensional array, the first element as the day and the second element as the type of time off. I'm at the point right now of trying to join how the calendar gets built with my string of days off placed in the array. I'm pretty sure I can pull each day off by month and year out of our time-keeping system and eventually place the data into a SQL table in the format I want it in. The end result will be a web page showing one full years worth of monthly calendars color coded to depict an employees time off days at a glance for managers to make time-off decisions about their employees. Instead of seeing a bunch of dates not knowing which ones were Fridays or Mondays, having it in a calendar will immediately show which employees habitually choose to be sick for convenient three day weekends most often.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply