Database design - how do I track information over time and query a table for latest data?

Question

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 Applications table and at first, I created a UnitTests column in the Applications table but I just realized that by simply keeping this up to date it will overwrite the history of unit test for that application.

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 UnitTestTracking which has the following columns:

• ID (primaryKey)
• application_ID(int)\
• date_added (datetime)
• unittestcount (int)

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.

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?

Essentially (in pseudo-SQL) I need something like:

"Select count of unit tests across all applications where the application data_added is the latest date added for that application id"

How you would write this query?

Answer 1

You can use auditing. This will create the logs you want.

To separate between different applications and unit tests you can create separate users for each application. It will simplify querying who tested what.

Answer 2

"Select count of unit tests across all applications where the application data_added is the latest date added for that application id"

I'm afraid all I can say it that this formulation-of-requirement seems necessarily flawed.

First, the only "free variable" in your query (i.e. the only parameter) seems to be "that application ID".

So your problem statement seems to be :

(1) Given an application ID, get me the latest (i.e. MAX(...)) date_added of that application ID. (2) Given that latest date, give me all the applications that have a date_added that is equal to the result of (1) (3) Given that set of applications, give me the count of unit tests "across those applications"

Second : with respect to "across those applications", I must say that neither my understanding of relational algebra nor my understanding of natural language helps me the remotest bit to understand what it is (PRECISELY) that you mean.

Answer 3

I think people seem to be making this harder than it is.

To resolve this you need two queries:

1. Find me the latest entry for each application ID
2. Using the latest entry for each application ID give me the sum of the unit test counts.

SQL for the first is:

SELECT application_ID, MAX(date_added) AS lastDateAdded FROM UnitTestTracking GROUP BY application_ID

For the second we make this work by nesting queries:

SELECT 
    SUM(unittestcount) 
FROM 
    UnitTestTracking JOIN 
    (SELECT 
         application_ID, MAX(date_added) AS lastDateAdded 
     FROM 
         UnitTestTracking GROUP BY application_ID) T 
    ON UnitTestTracking.application_ID = T.application_ID AND 
       UnitTestTracking.date_added = T.LastDateAdded

And that should give you what you need i.e. the current total number of unit tests.

Answer 4

Any of these 2 options will work for your scenario:

1 have the logic that adds the new unit test count for the application, insert the record in the history + update the application record's unit test count. Then use a simple select over the application records - history records have nothing to do in this scenario. This is best if you'll have a huge amount of records in the history.

2 use this query against the UnitTestTracking table directly

select application_id, unittestcount from UnitTestTracking u1 
where date_added = ( 
   select max(date_added) from UnitTestTracking u2 
   where u1.application_id = u2.application_id 
)

Answer 5

I think that original design is a bit off, hence the complexity. The design below suggests daily (or more frequent) entries, but only count for the day, for a specific application, by a specific person. The Kimball star schema allows for easy slicing and dicing by date, by month, by year, by application, by person, by job title etc.

For example, across all applications in years 2008, 2009, 2010

SELECT  sum(TestCount) AS "Test Count"
FROM    factTest AS f
        JOIN dimApplication AS a ON a.ApplicationID = f.ApplicationID
        JOIN dimPerson AS p ON p.PersonID = f.PersonID
        JOIN dimDate AS d ON d.DateID = f.DateID
WHERE [Year] BETWEEN 2008 AND 2010

Across all applications in year 2009, only on Fridays

WHERE [Year] = 2009 AND DayOfWeek = 'Friday'

Across all applications in year 2009, by person.

SELECT  FullName, sum(TestCount) AS "Test Count"
FROM    factTest AS f
        JOIN dimApplication AS a ON a.ApplicationID = f.ApplicationID
        JOIN dimPerson AS p ON p.PersonID = f.PersonID
        JOIN dimDate AS d ON d.DateID = f.DateID
WHERE [Year] = 2009
GROUP BY FullName

By application, by person, by month in year 2009, but only on weekends

SELECT  ApplicationName, FullName, [MonthName], sum(TestCount) AS "Test Count"
FROM    factTest AS f
        JOIN dimApplication AS a ON a.ApplicationID = f.ApplicationID
        JOIN dimPerson AS p ON p.PersonID = f.PersonID
        JOIN dimDate AS d ON d.DateID = f.DateID
WHERE [Year] = 2009 AND IsWeekend = 'Yes'
GROUP BY ApplicationName, FullName, [MonthName]

Across all applications for years 2000-2009, by year, by month, but only for tests done by a receptionist on Tuesdays.

SELECT  [Year], [Month], sum(TestCount) AS "Test Count"
FROM    factTest AS f
        JOIN dimApplication AS a ON a.ApplicationID = f.ApplicationID
        JOIN dimPerson AS p ON p.PersonID = f.PersonID
        JOIN dimDate AS d ON d.DateID = f.DateID
WHERE   [Year] BETWEEN 2000 AND 2009
        AND JobTitle = 'Receptionist'
        AND DayOfWeek = 'Tuesday'
GROUP BY [Year], [Month]

Across all applications, for year 2009, tests done on weekends by short people who own two or more cats.

SELECT  sum(TestCount) AS "Test Count"
FROM    factTest AS f
        JOIN dimApplication AS a ON a.ApplicationID = f.ApplicationID
        JOIN dimPerson AS p ON p.PersonID = f.PersonID
        JOIN dimDate AS d ON d.DateID = f.DateID
WHERE [Year] = 2009
      AND IsWeekend = 'Yes'
      AND IsShortPerson ='Yes'
      AND CatsOwned >= 2

Etc...

The syntax is sql server, but there is nothing special here.

UPDATE
Notice that FROM ... JOIN ... JOIN ... JOIN ... is always the same. All slicing and dicing is done via SELECT, WHERE, and GROUP BY -- no "complex queries" required.