I am a Business Analyst and have prepared tables/erd for a system we are implementing.
The context is essentially an employee management system, an employee can join the company, change positions, get promoted, demoted, terminated etc. All of this is required to be tracked for filtering and reporting purposes. Therefore we require historical tracking of records.
My recommendation and original design of the tables included a field called “Effective Date”, so essentially effectively from a date onwards a particular “Action” is valid.
Say for example, John joined an organisation as a consultant on the 1st Jan 2017 thus the action was he was hired, therefore the effective date is 1st Jan 2017 and he was a consultant for a certain period of time until he became a senior consultant on the 6th September 2017, thus the effective date is 6th September 2017 with an action of promoted for that record.
By the way we will also be performing calculations on the salary of the employee based on their position and other parameters so there will be derived fields and fields being referenced from other tables etc.
Now my boss and the Solutions Architect have advised not to use the “Effective Date,” my boss says there will be “problems” with the calculation but doesn’t elaborate, and the Solutions Architect says it would be easier to use a Start Date and an End Date instead of effective date. His rationale is if there’s no end date that action/event is active, but is inactive once an end date is provided.
My problem with this is that we’ll have to maintain an additional column that I feel is totally uneccessary.
What do the brains trust of StackOverflow advise??
Thanks 🙂
Advertisement
Answer
Definitely implement the end date. It is a tiny bit more work when writing but you only write it once, but you will report on it many many times and you’ll find that it makes everything so much easier (and faster) when the end date is already there on the record.
All over stackoverflow you will find questions about writing queries to find the end date of a given record when it is defined on the ‘next’ record rather than the ‘current’ record These queries are ugly and slow
If you look at the back end of enterprise systems like SAP you’ll find that records have start and end dates defined.
With regards to your colleagues comments about not using effective date: You don’t provide much info so I’ll guess. I’m guessing that there is a true ‘effective date’ when the thing happened but there is also another set of start and end dates which are the payroll effective dates that the change applies to. So if someone starts on the 1st, the payroll effective date might actually be the 15th. This might also be used for FTE calculations. Payroll and pay periods are really a big deal and quite complex so you shouldn’t underestimate the complexity there. If you’re including pay calculations in this system then at the very least you need to understand what effective payroll dates are.
You should not be afraid of storing four date columns instead of one. Databases are there to make things easy for you not harder.