title: Database Table Design for Group Values with Changing Status over Time
body: <p>I have the following groups that have a particular designation depending on the date:</p>
  
  <pre><code>Group 1:  3/30/2017 to present: status 'on'
  Group 2:  3/30/2017 to present: status 'on'
  Group 3:  3/30/2017 to present: status 'on'
  Group 4:  3/30/2017 to 6/1/2017: status 'off'; 6/2/2017 to present: status: 'on'
  Group 5:  3/30/2017 to present: status 'off'
  Group 6:  3/30/2017 to 7/10/2017: status 'off'; 7/11/2017 to present: status 'on'
  </code></pre>
  
  <p>I'm trying to translate this information into an effective database table so I can designate a change in status on a particular date.</p>
  
  <p>I have a process that runs daily in near real time that checks the status of each group and undertakes various processes based on the status.</p>
  
  <p>I have come up with the following though I think it is not sufficient:</p>
  
  <pre><code>Group    Effective Date    Termination Date   Status
  
  Group 1    '2017-03-30'        NULL            On
  Group 2    '2017-03-30'        NULL            On
  Group 3    '2017-03-30'        NULL            On
  Group 4    '2017-03-30'     '2017-06-01'       On
  Group 4    '2017-06-02'        NULL            Off
  Group 5    '2017-03-30'        NULL            Off
  Group 6    '2017-03-30'     '2017-07-10'       Off
  Group 6    '2017-07-11'        NULL            On
  </code></pre>
  
  <p>So if I run my daily process historically, I want it to be able to consult the table and determine the status of the group.  If I am running my process in real time, I want to be able to consult the table and determine the status.  If I want to change the status at a particular point in time, I enter a termination date for the Group and status and start a new line.</p>
  
  <p>I can't imagine this is a good way to do this. </p>
  
  <p>Looking for insights.</p>
  
  <p>Thanks in advance.</p>