Design question regarding junction tables

[bg_ie]

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

[Erland Sommarskog]

(bg***@yahoo.com) writes:

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?

Without knowing the full story, it's difficult to tell. I can understand
"Function" and "Test", but "Scene" is more opaque to me. Nevertheless,
it seems to me that you should rather start from Functions and Scenes.
A function can have many scenes, and the a scene could apply to more
than one function? I guess so, since you say that you need a junction
table.

But can a FunctionScene have more than one Test? Can one Test have more
have one FunctionScene? Or tests not tied at all to the combination
of functions and scenes?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx