|
|
Posted by Erland Sommarskog on 01/18/08 22:41
(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/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|