title: Database design - how do I track information over time and query a table for latest data?
body: <p>We are trying to track our applications in our department and our unit test usage so I have created a database to keep track of this.  I have an <strong>Applications</strong> table and at first, I created a <strong>UnitTests</strong> column in the <strong>Applications</strong> table but I just realized that by simply keeping this up to date it will overwrite the history of unit test for that application.</p>
  
  <p>Since I want to be able to extract the data here over time to produce charts that show progress, I realized that I needed a separate table called <strong>UnitTestTracking</strong>  which has the following columns:</p>
  
  <ul>
  <li>ID (primaryKey)</li>
  <li>application_ID(int)\</li>
  <li>date_added (datetime)  </li>
  <li>unittestcount (int)</li>
  </ul>
  
  <p>This way, people can add new entries once a week or once a month in this table and we will have a history over time to show progress for each application and the total across all applications.</p>
  
  <p>The one issue now is: How would I write a query against this table to get the total count across all applications since different applications will be adding entries in this table at different times?</p>
  
  <p>Essentially (in pseudo-SQL) I need something like:</p>
  
  <p>"Select count of unit tests across all applications where the application data_added is the latest date added for that application id"</p>
  
  <p>How you would write this query?</p>