Design question regarding junction tables

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

Re: Design question regarding junction tables

(bg_ie@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx