By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,854 Members | 2,004 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,854 IT Pros & Developers. It's quick & easy.

Design question regarding junction tables

P: n/a
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

Jan 18 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
(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
Jan 18 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.