Xrhrft

TL;DR

Is it better practice to keep archived data of 3 different entities with the exact same data types in 3 different tables or should they be put in a single table and a foreign key be added for the purpose of differentiating between the 3 types of data

I am in the process of developing a database for storing employee work hour tracking. Since the number of hours depends on when the employee has checked-in/checked-out of work and not a log of hours per day, I have created a couple of views that show the number of work hours, the difference in hours from what the employee is expected to be working for every day/week/month.

The problem is that doing lots of time calculations for every single employee on the fly is quite heavy on my database, so what I have done is that I am only calculating the work hours for this day on the fly and am storing archive data for days/weeks/months in separate tables then am retrieving the data together so that the query does not take too long.

What I am asking is that, is it most efficient to store the Day/Week/Month Archives in 3 separate tables or whether it would be more clean and efficient to store them in a single table since all 3 tables have the exact same criteria with the exact same data types (Unique ID, User ID, Date, Number of hours worked on that day, Difference in hours from what the employee should be working).

On one hand, a foreign key could be added (lets say ArchiveType 'Day'/'Week'/'Month') and all of the data could be stored in a single table, making the ERD look a lot cleaner as well as having 2 less tables to worry about when integrating the DataBase with the user interface.

On the other hand, all views/procedures/functions that interact with those 3 tables at the moment will have to be rewritten as well as procedures for updating the Archived data in case of a change will have to get a lot more complicated.

If there is a better approach to this problem than creating archive tables for caching data that is not regularly updated, please tell me.

The way that I have it right now (3 separate archive tables) the queries take less than half a second, which is acceptable. However, since I have to add a couple new features (department management, check-in through an RFID device) I decided to draw out a full ERD diagram of the database and looking at it now, I am realizing that the ERD does not look very clean.

Also since I am also having to write the frontend for it in Java EE with JPA and have to create Persistence Units for every single Table/View, the project could be greatly simplified if the number of tables/views was reduced.

I have tried to use a single Archive total hours per day per employee and then have a view which sums hours per week/month without their results being Archived. The problem with that approach is that I do not only keep the total hours for the period, but also the difference of the total hours and the number of hours the employee has to work in that period.

In addition to that there is no easy way to know when the week starts/ends. Yes there are week numbers in a year, but a week can start in one year and end in the next, meaning that I have to use a function which calculates the start/end of a week before doing the sum. Meaning that if I follow that approach the query time for a period of 6 months takes about 10 seconds, which is not an acceptable time for a web-app that has to return data instantly.