#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    81
    Rep Power
    2

    Employee Table Setup


    Hi everyone,

    I have a created (with lots of help) a database with several tables to store the vacation, lieu days, and paid leave time for my co-workers.

    This data is then displayed as a calendar for staff and managers to see what days/time is available for anyone requesting time off.

    Staff can look at the webcalendar and see if a day is available or not based on the background color. Red = closed, green = Available, Yellow = Closed for supervisors. Currently this color scheme only works for tracking vacation time - which is one entire block of 24 hours. Any of the other selections do not change the color of the background.

    The major flaw in how I have put this together is, I am unable to track anything under an entire shift off. This is where it might be confusing, I will try to explain how our shifts work as clearly as I can.

    We work 24 hour shifts 7am - 7am the next day.
    1 vacation day includes the entire 24 hours.
    1 lieu day is 12 hours, either the 7am - 7pm, or the 7pm-7am portion.
    Paid leave can be any amount of time, at any point during the shift.
    The maximum number of people off, at any given time during the 24 hours is 5.
    The maximum number of supervisors off at any given time during the 24 hours is 3 (this is included in the 5 above)

    I am assuming that I will have to restructure my database to make all of this work.

    I currently have a seperate table for the paid leave, vacation, and lieu day selections. And a seperate table for employees.

    Does anyone know of examples of this type of thing? Or have some suggestions on how I could structure this to be able to track what I described.

    Thank you.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    141
    Rep Power
    65
    You probably need to sit down and define what data you have that you need to store (databases are for storing the raw, available, data.) Then define how to produce the report/calendar using that data.

    The user/employee table should only hold the unique/one-time employee information. The who, what, when, where, and why information about the employees.

    Next, you need a (one) table to record the accumulation and use of time off. Employees can accumulate or be given an amount of each type of time off, with a start and end date/time it is available, and they can request/use an amount of each type of time off, with a start and end date/time when it is/was requested/used, and if it was earned/made available (a credit) or requested/used (a debit). There would be a new row for each transaction that credits or debits each type of time off for each employee. This table will tell you how much of each type of time off each employee has available or has requested/used on any date/time and would be used when producing the report/calendar of available time off and limiting the requested time off for each employee (they cannot request more then they have available.)

    In general, to produce the report/calendar you have described, you would just take the total available time off for any date/time period and subtract the requested time off for the same date/time period, for both supervisors and all people, using the smallest date/time granularity that you need to keep track of. If it wasn't for the paid leave, you would need to do this for half shift periods and would need to display the result for each half shift.

    The problem becomes the paid leave. If this can start and end on any hour (or minute) and can span the half shift time, then for any period that has requested paid leave in it, if the available time off limit has been reached, you will instead need to calculate and display a daily schedule down to the hour (or minute), so that someone else can request either a lieu day or paid leave during the part of the day that the available time off limit hasn't been reached.
    Last edited by DSmabismad; July 12th, 2018 at 02:03 PM.
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2016
    Posts
    81
    Rep Power
    2
    I have the employee table with the following information:
    Code:
    CREATE TABLE `employee` (
      `employee_id` int(11) NOT NULL,
      `name` varchar(50) DEFAULT NULL,
      `startdate` date DEFAULT NULL,
      `station_id` int(11) DEFAULT NULL,
      `rank_id` int(11) DEFAULT NULL,
      `platoon_id` int(11) DEFAULT NULL,
      `vac_weeks` int(11) DEFAULT '3',
      `btt_total` decimal(10,2) DEFAULT '0.00',
      `retired` varchar(10) DEFAULT NULL,
      PRIMARY KEY (`employee_id`),
      KEY `FK_employee_platoon` (`platoon_id`),
      KEY `FK_employee_rank` (`rank_id`),
      CONSTRAINT `FK_employee_platoon` FOREIGN KEY (`platoon_id`) REFERENCES `platoon` (`platoon_id`),
      CONSTRAINT `FK_employee_rank` FOREIGN KEY (`rank_id`) REFERENCES `rank` (`rank_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    The Vacation (Full Day Table looks like this:
    Currently when a user selects a day, they get a deduction of .5 from 'vacation_weeks' in the employee table. When they reach zero, no more picks can be made by that person.
    Code:
    CREATE TABLE `vacation_picks` (
      `pick_id` int(11) NOT NULL AUTO_INCREMENT,
      `employee_id` int(11) DEFAULT NULL,
      `platoon_id` int(11) DEFAULT NULL,
      `_date1` date DEFAULT NULL,
      `week_cost` decimal(10,1) DEFAULT '0.5',
      `tracking` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
      PRIMARY KEY (`pick_id`),
      KEY `FK__employee` (`employee_id`),
      KEY `FK_vacation_picks_platoon` (`platoon_id`),
      KEY `FK_vacation_picks_schedule` (`_date1`),
      CONSTRAINT `FK__employee` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`),
      CONSTRAINT `FK_vacation_picks_platoon` FOREIGN KEY (`platoon_id`) REFERENCES `platoon` (`platoon_id`),
      CONSTRAINT `FK_vacation_picks_schedule` FOREIGN KEY (`_date1`) REFERENCES `schedule` (`_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=8960 DEFAULT CHARSET=latin1;
    Stat/Lieu Days Table:
    User picks a day, and whether they want the am/pm portion of the shift. 1 selection per person
    Code:
    CREATE TABLE `stat_picks` (
      `pick_id` int(11) NOT NULL AUTO_INCREMENT,
      `employee_id` int(11) DEFAULT NULL,
      `platoon_id` int(11) DEFAULT NULL,
      `_date` date DEFAULT NULL,
      `am_pm` varchar(50) NOT NULL,
      PRIMARY KEY (`pick_id`),
      KEY `FK_stat_picks_employee` (`employee_id`),
      KEY `FK_stat_picks_employee_2` (`platoon_id`),
      KEY `FK_stat_picks_schedule` (`_date`),
      CONSTRAINT `FK_stat_picks_employee` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`),
      CONSTRAINT `FK_stat_picks_employee_2` FOREIGN KEY (`platoon_id`) REFERENCES `platoon` (`platoon_id`),
      CONSTRAINT `FK_stat_picks_schedule` FOREIGN KEY (`_date`) REFERENCES `schedule` (`_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=959 DEFAULT CHARSET=latin1;
    Bank Time Picks:
    Like you said this is the challenging one - well they all are for me after vacation picks. The user/employee can select 1-24 hours at a time. Starting on the hour or half hour.
    The amount of hours they have available is stored in the employee table.
    Code:
    CREATE TABLE `btt_picks` (
      `pick_id` int(11) NOT NULL AUTO_INCREMENT,
      `employee_id` int(11) DEFAULT NULL,
      `platoon_id` int(11) DEFAULT NULL,
      `_date` date DEFAULT NULL,
      `start_time` decimal(10,2) DEFAULT NULL,
      `end_time` decimal(10,2) DEFAULT NULL,
      `duration` decimal(10,2) DEFAULT NULL,
      PRIMARY KEY (`pick_id`),
      KEY `FK_stat_picks_employee` (`employee_id`),
      KEY `FK_stat_picks_employee_2` (`platoon_id`),
      KEY `FK_stat_picks_schedule` (`_date`),
      CONSTRAINT `btt_picks_ibfk_1` FOREIGN KEY (`employee_id`) REFERENCES `employee` (`employee_id`),
      CONSTRAINT `btt_picks_ibfk_2` FOREIGN KEY (`platoon_id`) REFERENCES `platoon` (`platoon_id`),
      CONSTRAINT `btt_picks_ibfk_3` FOREIGN KEY (`_date`) REFERENCES `schedule` (`_date`)
    ) ENGINE=InnoDB AUTO_INCREMENT=914 DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT;
    Do you think all of this needs to be changed/ made again to match hours of the day? Convert all the values in employee (Vacation, BTT, etc) to be hours instead of the sort of point system I am using now?

    Should I scrub the above and try to start from scratch?

    Thank you so much for your detailed reply!

IMN logo majestic logo threadwatch logo seochat tools logo