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

Posted by mikeschuld on Stack Overflow See other posts from Stack Overflow or by mikeschuld
Published on 2009-11-18T18:41:05Z Indexed on 2010/05/06 3:08 UTC
Read the original article Hit count: 287

Filed under:

mysql

|

parent-child

|

orm

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?

© Stack Overflow or respective owner

Related posts about mysql

How to remove MySQL completely with config and library files on ubuntu 12.04 gnome 3.0

as seen on Ask Ubuntu - Search for 'Ask Ubuntu'
I tried everything till now: sudo apt-get remove mysql-server mysql-client mysql-common sudo apt-get purge mysql-server mysql-client mysql-common sudo apt-get autoremove and even more commands... But whenever I am trying to locate mysql. I get a no. of files related to mysql command: shell>>… >>> More
mysql: Cannot load from mysql.proc. The table is probably corrupted

as seen on Server Fault - Search for 'Server Fault'
Mysql was started: /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 but when I'm trying to do something: ERROR 1548 (HY000) at line 1: Cannot… >>> More
Why is there a /etc/init.d/mysql file on this Slackware machine? How could it have gotten there?

as seen on Server Fault - Search for 'Server Fault'
A client of my IT-consulting service owns a web-development shop. He's been having problems with a Slackware 12.0 server running MySQL 5.0.67. The machine was set up by the client's sysadmin, who left on bad terms. My client no longer employs a sysadmin. As far as I can tell, the only copy of MySQL… >>> More
mysql: Bind on unix socket: Permission denied

as seen on Server Fault - Search for 'Server Fault'
Can't start mysql with: sudo /usr/bin/mysqld_safe --datadir=/srv/mysql/myDB --log-error=/srv/mysql/logs/mysqld-myDB.log --pid-file=/srv/mysql/pids/mysqld-myDB.pid --user=mysql --socket=/srv/mysql/sockets/mysql-myDB.sock --port=3700 120222 13:40:48 mysqld_safe Starting mysqld daemon with databases… >>> More
MySQL – Learning MySQL Online in 6 Hours – MySQL Fundamentals in 320 Minutes

as seen on SQL Authority - Search for 'SQL Authority'
MySQL is one of the most popular database language and I have been recently working with it a lot. Data have no barrier and every database have their own place. I have been working with MySQL for quite a while and just like SQL Server, I often find lots of people asking me if I have a tutorial which… >>> More

Related posts about parent-child

automation of data format conversion to parent child format

as seen on Stack Overflow - Search for 'Stack Overflow'
This is an excel sheet which has only a single column filled for each row. (explanation : all CITY categories fall under V21 , all handset categories fall under CityJ and so on ) V21 CITYR CITYJ HandsetS HandsetHW HandsetHA … >>> More
dataset - set parent child relations

as seen on Stack Overflow - Search for 'Stack Overflow'
Hello all I am trying to set the relations of rows in DataSet and then to show that relation in XTraaTreeList as tree with relations. | --| ----| but i get | | | I am doing this code but i get a view without any relations i get them all in one level. Any idea what i am doing wrong ? … >>> More
How to reset parent child relationship between nested div

as seen on Stack Overflow - Search for 'Stack Overflow'
I am new to CSS designing and not aware of most of the properties of CSS. I am creating a layout for a web page. I am using div in my layout. My structure is somewhat like this <div id="content1_bg"> <div> <div class="content1_title_img_div"></div> … >>> More
Hibernate: Parent/Child relationship in a single-table

as seen on Stack Overflow - Search for 'Stack Overflow'
I hardly see any pointer on the following problem related to Hibernate. This pertains to implementing inheritance using a single database table with a parent-child relationship to itself. For example: CREATE TABLE Employee ( empId BIGINT NOT NULL AUTO_INCREMENT, empName VARCHAR(100) NOT NULL… >>> More
Hibernate: Parent/Child relationship in a single-table

as seen on Stack Overflow - Search for 'Stack Overflow'
I hardly see any pointer on the following problem related to Hibernate. This pertains to implementing inheritance using a single database table with a parent-child relationship to itself. For example: CREATE TABLE Employee ( empId BIGINT NOT NULL AUTO_INCREMENT, empName VARCHAR(100) NOT NULL… >>> More