Design for a Testing system I want to redesign a part of my system. I got the following objects:  

Site:
id  
Name

Site Version:
id
idSite
Name

Task:
id
idSite
Name

Test:
id
idTask
Name

TestResult:
idTest
idSiteVersion
dateStarted
dateEnded
result


Two problems:


You can see that I have a circle in the definitions. The problem is, I got Tasks defined for Sites, and also through the TestResult &gt; SiteVersion &gt; Site I can get back to Site again.
The main query to answer is, for a given Site, show all SiteVersion and their compatibility to Tasks defined for them.
I've solved 2. using sub-querys

SELECT
  tr1.id AS id,
  dsv.idSite_id AS idSite,
  tr1.idSiteVersion_id AS idSiteVersion,
  tr1.idTest_id AS idTest,
  tr1.result AS result,
  tr1.timeStarted AS timeStarted,
FROM 
  dash.testresult AS tr1 
  NATURAL JOIN (
    SELECT idTest_id, idSiteVersion_id, MAX(timeEnd) AS timeEnd
    FROM dash.testresult
    WHERE timeEnd IS NOT NULL
    GROUP BY idTest_id, idSiteVersion_id
  ) AS tr2
  JOIN dash.siteversion AS dsv
    ON dsv.id = tr1.idSiteVersion_id
  JOIN dash.test AS dtest
    ON dtest.id = tr1.idTest_id AND dtest.ignored = 0