I need to store and analyze employee's work time.
Currently, I've developed this table structure
CREATE TABLE `hirefire` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `employeeid` int(10) unsigned NOT NULL, `hired` date NOT NULL, `fired` date DEFAULT NULL, `firereason` enum('fired','left','parental','vacation','death') COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `hired` (`hired`), KEY `fired` (`fired`), KEY `employee` (`employeeid`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
fired are self-explanatory (
fired may be NULL when a person was hired but not yet fired).
- 'fired' - forced to be fired by the boss
- 'left' - left job by his own will
- 'parental' - parental leave
- 'vacation' - his non-paid vacation
- 'death' - he or she died
This my table scheme may be (I think) good to check whether a given employee is listed as working at least a day in a given month (BTW, an SQL code for this would be appreciated),
But now a complexity comes out:
We will also need to be able to count employee's total working years. Note that parental leave and non-paid vacations should be not subtracted from the working years. This time my database structure goes non convenient for this kind of calculation.
Which DB structure would you suggest to use? (Is my (above) variant is the best structure for these tasks, or can it be improved?)
What's about allocating a whole DB row for every hire/fire event (that is having a separate row for hire data and fire date not one the same row for paired fire/hire)? For which tasks this would be better and for which worse?