List:General Discussion« Previous MessageNext Message »
From:Cal Evans Date:June 6 2002 9:15pm
Subject:RE: A design question
View as plain text  
Chuck,

The way to do this is to create a primary key (PK) in your main table. (I
usually use bigint auto_increment with a name of tablenameID)

Then in your child table, you put the same field in and store the key to the
parent record.

i.e.

movie
-----
movieID
=====
title
-----

actor
-----
actorID
=====
movieID
name
-----


Now select * from actor where movieID = 1

gives me all the actors in movie #1.

Select movie.title, actor.name
from movie left join actor on movie.movieID = actor.movieID
where movieID=1

will give me multiple rows, one for each actor in movie 1.  The movie title
will be repeated on each record but that's acceptable.

Anyhow, you are on the right track.  Don't be surprised if you have several
child tables by the time you are finished. (actor, director, genera,
language, etc.)

=C=

*
* Cal Evans
* Journeyman Programmer
* Techno-Mage
* http://www.calevans.com
*


-----Original Message-----
From: Chuck PUP Payne [mailto:cepayne@stripped]
Sent: Thursday, June 06, 2002 3:45 PM
To: mysql@stripped
Subject: A design question


Hi,

I have a design question for mysql database that  I am wanting to create a
my movies collection. I was going to do as one-to-one database, but I see
now that I need to do as a one-to-many. I have never done a one-to-many but
I see that is the way to go.

I guess then is can someone show me where I can see one-to-many example, and
how you do sql statements? To make it clear, I know that I have to do table
for the movie, basic information. Then a table for actor/actress.

I know I need a field in both tables that are the same so they are related.
I am sorry if I don't make sense, but my head is spinning with trying to
understand on one-to-many works.

Chuck Payne
Magi Design and Support


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <mysql-thread111248@stripped>
To unsubscribe, e-mail <mysql-unsubscribe-cal=calevans.com@stripped>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


Thread
A design questionChuck PUP Payne6 Jun
  • RE: A design questionCal Evans6 Jun