How to model parent to child pair in MySQL (SQL)

Question

I have a data model that includes element types Stage, Actor, and Form. Logically, Stages can be assigned pairs of ( Form <---> Actor ) which can be duplicated many times (i.e. same person and same form added to the same stage at a later date/time).

Right now I am modeling this with these tables:

Stage
Form
Actor

Form_Actor    
_______________
|Id           |
|FormId       | --> Id in Form
|ActorId      | --> Id in Actor


Stage_FormActor
__________________
|Id              |
|StageId         | --> Id in Stage
|FormActorId     | --> Id in Form_Actor

I am using CodeSmith to generate the data layer for this setup and none of the templates really know how to handle this type of relationship correctly when generating classes. Ideally, the ORM would have Stage.FormActors where FormActor would be the pair Form, Actor.

Is this the correct way to model these relationships. I have tried using all three Ids in one table as well

Stage_Form_Actor
______________
|Id          |
|StageId     | --> Id in Stage
|FormId      | --> Id in Form
|ActorId     | --> Id in Actor

This doesn't really get generated very well either. Ideas?

Answer 1

I don't know anything about CodeSmith, but your database schema for defining inherent many-to-many relationships between these three entities is correct (StageFormActor one is best).

One thing to note is that you might want to define a compound primary key to that association table (instead of using an artificial ID primary key)