測試系統設計

我想重新設計我係統的一部分。我得到了以下對象：

Site:
id  
Name

Site Version:
id
idSite
Name

Task:
id
idSite
Name

Test:
id
idTask
Name

TestResult:
idTest
idSiteVersion
dateStarted
dateEnded
result

兩個問題：

1. 你可以看到我在定義中有一個圓圈。問題是，我被Tasks定義為Sites，並且通過TestResult > SiteVersion > Site我可以Site再次返回。
2. 要回答的主要查詢是，對於給定的Site，顯示所有內容SiteVersion及其Tasks為它們定義的兼容性。

我已經2.使用子查詢解決了

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

引用自：https://dba.stackexchange.com/questions/7084