How to model parent to child pair in MySQL (SQL)
- by mikeschuld
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?
