Unanswered: qualified association / relationship, one-to-#, one-to-3 relationship
I am implementing a database with very few tables (lets keep it simple).
I foresee two solutions, one that I can make, other that I think is better but I would need some help to implement it.
The problem is the following: each CARD has a Name and 3 SCENES (out of 9), each SCENE has a description. The order of scenes is important.
A Form would be like this:
Card Number: 33
Scene 1: 2
Scene 2: 1
Scene 3: 9
I foresee two solutions.
Solution 1 (two tables):
table card: cardNumber (PK), name, scene1 (FK), scene2 (FK), scene3 (FK)
table scene: sceneNumber (PK), description.
Comments: In this solution I have 3 one-to-many associations. I can make a form just like the picture with combo boxes to select scenes. The order of each scene in a card is given by its name (scene1, scene2, scene3).
Solution 2 (qualified association):
I would prefer to do something like this:
I thougth of implementing it with: +----+.1..3.+-----------+.n..1.+-----+ |Card|------|CardToScene|------|Scene| +----+......+-----------+......+-----+
table Card: cardNumber(PK), name
table CardToScene: id(PK), cardNumber(FK), sceneNumber(FK), order
table Scene: sceneNumber(PK), description
Comments: Since there is no way to define a one-to-three association,
how can I enforce the user to enter three and only three scenes?
how can I present the form just like the picture above, with combo boxes to select the scenes? (using subforms the user would be presented with rows)
Can I enter the data in temporary fields of the form and then create 3 records in the CardToScene table with the values?
how can I have a query where I would get the scenes in boxes side by side? (only I know the result of the query is a recordSet with 3 records).
Does anyone have yet another idea to solve the problem?b
Does any one had the same problem?
Can any one help me?
Your second structure is better. To limit your records to only three scenes there are several ways. The simplest is probably to provide a pop up form to enter a scene, recording the entries in a sub form with data entry set to no. You could then requery the subform on closing your pop up form.
In the procedure to open the popup form you could count the records in the sub - form if they are three then pop up a message and offer to edit an existing record or something similar.