title: Should I use Effective Date or Start Date and End Date for historical recording?
body: <p>I am a Business Analyst and have prepared tables/erd for a system we are implementing.</p>
  
  <p>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.</p>
  
  <p>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. </p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>My problem with this is that we'll have to maintain an additional column that I feel is totally uneccessary.</p>
  
  <p>What do the brains trust of StackOverflow advise?? </p>
  
  <p>Thanks :)</p>