Normalization 3 layers of tables

[barneyf]

All of the normalization info I can find shows only 2 levels of tables.  I have three levels (Summary, Stage, Tests) for a test results database.  The top layer is the "Summary" and defines overall test information such as DUT serial number etc.  Each Summary can have multiple test Stages (1 Summary->many Stages).  Each stage can have one of several different test results (1 Stage->1 Performance OR 1 Stage-> 1 Timeout, etc).  I am using InnoDB and linking the sub-tables back to the higher level via a foreign key.  The structure is:

Summary (Level 1)
  SummaryID Auto Int (PK)
  SerialNum VARCHAR(20)
  others....

  Stage (Level 2)
    StageID Auto Int (PK)
    SummaryID Int (FK -> Summary.SummaryID)
    StageNum TINYINT
    others.....

    Performance (Level 3a)
      PerformanceID Auto Int (PK)
      StageID Int (FK -> Stage.StageID)
      RandomWrite DECIMAL(7,2) SIGNED
      others....

    TimeOut (Level 3b)
       TimeOutID Auto Int (PK)
       StageID Int (FK -> Stage.StageID)
       TimeoutLimit
       others...

 

I want to display the information from specific Performance rows along with the corresponding information from the Stage and Summary table rows.  For example:

 

SerialNum StageNum RandomWrite
ABC123    1             100.1
ABC123    2              89.6
ABC123    3              75.2
ZYX321    1              99.2
ZYX321    2              45.6

 

My PHP code joins the Performance and Stage, but I am having trouble getting to the Summary level.  In trying different JOIN approaches, I am not sure my design is a good one.  For example, should I include the SummaryID in the Performance table?  That way I can skip over the Stage level to get to the Summary level.  But then I am not sure if that is properly normalized.

 

In a similar situation in the past I had a pre-defined database and used multiple queries to get the row info from the database.  Then I used a lot of PHP code to mash the data together to get what I wanted.  Not elegant or efficient.  Since I am defining the database, I want to make the database definition support elegance and efficiency.

 

Any discussion?