New to SQL Database design, however i have a quick question that was already discussed in another forum post however I would like to take it one step further. The original post was as follows:
"I just followed a tutorial which gives the example of a many-to-many relationship. In this case, how to allow movies to have multiple Genres.
However, I would like to expand on this example, and try something slightly more complicated. Basically I am looking to something like MANY-to-MANY-to-MANY
or MANY-to-MANY-to-ONE.
Suppose I want to create a table called SCENE. SCENE can have more than one ACTOR, and each ACTOR can have more than one ACTION. I wonder how would I do something like this?
At first I tried doing a separate many-to-many relationship table for ACTOR, and another for ACTION. So I created two joining tables (SceneToActor and SceneToAction). This way I can add multiple actors and actions to a scene. However, I can't tell which actor
is doing what action in the scene.
I wonder if the answer would be to create a many-to-many relationship between the SceneToActor table and the Action Table, so I would have something like SceneToActorToAction as the joining table."
The answer to this question was given as:
where you have an intermediate ScenesActors table that just lists the SceneID and ActorID (alongwith its own ID field [SceneActorID] as primary key).
You then have the ScenesActorsActions table which has the SceneActorID and ActionID (along with its own ID field [SceneActorActionID] as PK).
The ScenesActors table lists each actors are in which scenes.
The ScenesActorsActions table lists what they do.
Scenes --< ScenesActors >-- Actors
|
|
^
Actions --< ScenesActorsActions
However, in the design of my database, say Actions already had a one-to-many relationship with another table titled ActionPossibilities in which every row was a possible action that could take place (essentially making the possible actions that could
occur dynamic). How then would you effectively create the relationship described above? The problem i am running into is that the primary key for the Actions table is used as a foreign key in the ActionPossibilities table. Is something like this possible under
the current design?
Thanks for the help!
