Structuring tables to for tracking employee attandance and leave (1 Viewer)

[Kpaw]

I am building a database to track contract employee attendance as well as leave taken.

As contract employees, persons employment have a specific start and definite end date. In this regard a person may have a contract for three years starting on 15 August 2015 and is to end 14 August 2018.

Using the specific contract period to represent the general reality, during that contract period, the employee is entitled to 14 days sick leave per calendar year which if not utilized does not go over to another calendar year.

However, for vacation leave, the person earns either 15, 20 or 25 days per year which if not taken in the particular year rolls over into the ensuing year. So for instance, if an employee is entitled to 15 days in year but does not take any or takes part of the vacation leave, the unutilized leave is added to the eligibility for the next year. Note that for vacation leave, the year is determined by the anniversary date of engagement.

My challenge is who to structure the tables to satisfy the requirements as described, so that I can obtain for any employee, leave taken and the balance of leave available to the employee.

Thanks for your assitance.

 

[plog]

So give it a shot. What do you have so far? Can you post a screenshot of your relationships?

 

[Kpaw]

I have attached a pdf file showing the tables and the relationships that I have created.

 

[plog]

A few things:

1. You shouldn't have multiple paths among tables. There should only be one way to travel from one table to another. I see 3 ways to get from tblEmployee to tblLeaveEligibility--1 directly and 2 indirectly. That shouldn't be possible. I don't know which is correct, but 2 of them shouldn't exist.

2. You shouldn't have tables with 1 field of real data--IDs are generally not real data. For example, you don't need tblGender. Instead you would just store whatever value you have in the Gender field in tblEmployee. I believe you shouldn't have tblYear nor tblTypeOfLeave either.

3. You shouldn't store calculated values. That means Cummulative_Leave_Taken and Cumulative_Leave_Remaining shouldn't be fields in a table. Instead you create a query to determine them.

Those are the issues I see with your tables. I also see 2 issues in your description:

...the employee is entitled to 14 days sick leave per calendar year...

A. Calendar Year is 1/1 - 12/31. What you wrote above is that if I start on 12/1/2014 and my contract ends 12/1/2015 then I get 28 sick days for my 1 year contract. I get 14 in 2014 (12/1/2014 - 12/31/2014) and 14 in 2015 (1/1/2015 - 12/1/2015). Is that correct? Did you really mean calendar year for sick days?

...However, for vacation leave, the person earns either 15, 20 or 25 days per year..

B. You didn't specify when vacation accrues like you did for sick leave. Is it calendar year, or contract year? If I start on 8/14/2015 do I automatically get my vacation allocation on my first day and then my second allocation on my anniversary? Or is it calendar year as well?

 

[Kpaw]

A. Calendar Year is 1/1 - 12/31. What you wrote above is that if I start on 12/1/2014 and my contract ends 12/1/2015 then I get 28 sick days for my 1 year contract. I get 14 in 2014 (12/1/2014 - 12/31/2014) and 14 in 2015 (1/1/2015 - 12/1/2015). Is that correct? Did you really mean calendar year for sick days?

Yes sick days are for calendar years. However, the sick days in one calendar year do not roll over to a next year. Therefore in the example of the contract for one year starting 12/1/2014 and ending 12/1/2015, I would be entitled to 14 days in 2014 and 14 days in 2015.


B. You didn't specify when vacation accrues like you did for sick leave. Is it calendar year, or contract year? If I start on 8/14/2015 do I automatically get my vacation allocation on my first day and then my second allocation on my anniversary? Or is it calendar year as well?

Vacation leave accrues for contract year. However, one cannot take vacation leave before one year of the contract has finished. Vacation leave is accrued at a given rate per month (eg. 1.5 days) and it is only after one has accumulated vacation leave that one is able to take same. Unutilized vacation leave in one year rolls over to the following year and is added to the vacation leave eligibility of that year to give the total leave available to the employee in that year,

 

[plog]

Have you addressed issues 1-3? Those are issues with the table relationships, A & B was just to help my understanding of the details of your project.

Looking over this again, I believe you should have just one table for Leave acquired and leave taken. It would work like a check book--there would be credits given (vacation/sick days accrued) and debits (vacation/sick days used). That way you would be able to know exactly what the balance was for any point in time.

 

[Kpaw]

Re issues 1-3, I have addressed those. I am working on creating the table to track leave acquired and taken then to test it and indicate the results.

Thanks a lot for the assistance.