Posted by bg_ie on 01/18/08 11:54

I'm designing a database with 3 tables called Function, Test and
Scene.

A Function has multiple Tests, but a Test has only one Function. A
many to many relationship exists between Test and Scene therefore I
need a junction table between these two tables - giving 4 tables in
total. The Test table would store a foreign key, the primary key of
the Function table.

There is a problem with design though and that is that Functions and
Scenes are actually defined before the Test is defined. Therefore it
should be possible to create a Function and add to id its Scenes,
before Tests have been defined. In other words, Scenes are as much a
part of a Function as they are of Tests. Tests are in fact only of
relavence to testers. Anyway, to satisfy this scenario, a Junction box
is also needed beween Function and Scene. This creates a loop between
all tables.

Is this a good approach? Any other suggestions or advice on the
matter? Any advice regarding data integrity?

Thanks,

Barry

[Back to original message]