March 8, 2016 at 5:35 am
I got a database that stores weekly payments and recently I was asked to get some "numbers" out of it... All chaos.
Here's the story of this morning glory:
Frontend-wise:
A spreadsheet is loaded on a temporary table (that actually is a regular table). Spreadsheet has payments for a number of people and weeks. This is reviewed and then transfered to another table that is the one that holds the batch and finally records are "posted" to the definitve table.
This last table has 52 or 53 records per person per year (amount of weeks in a year [This records are added for all active persons at the begining of the year]) and basically store the person ID and the amount paid per week (if anything).
In theory the data on the records table should match the one in the table before since its solely the representation of this batch being paid/posted. But it just doesnt happen. Totals dont match and there's all sorts of evil within, like varchar Foreign Keys, duplicated year record batch, 1-51 week years (?), and thats just to name a few.
I feel most of this insanity has to do with the frontend being so old but also its sort of weird to have this 52/53 records a year...
So I was wondering if anyone here might have a different approach, how should this data be organized?, is it really necessary to hold a record a week per person even if they are only gonna pay 4 o less?
I got a few ideas but I would like to see what you think about it.
Regards.
March 9, 2016 at 2:29 pm
Short answer to a long question:
If the salaries are paid on a weekly basis, and they are not guaranteed always the same every week, and they need to be stored, then yes: you will get 52 or 53 rows per year.
A year has approximately 365,2425 days per year, that is not an exact multiple of 7. No way to change that.
I have no doubt that your application consists of a whole lot of mysery, held together by duct tape and hope. But storing weekly data in a table that has 52 or 53 rows per employee per year might well be its only redeeming quality, not its biggest fault.
EDIT: Upon once more rereading your post - storing those 52/53 rows up front at the start of the year is not necessarily a good idea, and you could also consider if you need rows when no salary was paid, e.g. because the employee was on unpaid leave for the entire week, or because their contract started/ended during the year.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply