Results 1 to 2 of 2

Thread: design help

  1. #1
    Join Date
    Jan 2003
    Posts
    2

    design help

    I am creating an application for my personal website that will catalog my movies using PHP and MySQL. I am having some problems designing some of the tables. Below is some sample data that I have for each movie:
    title: Air Force One
    rating: R
    genre: Action
    runtime: 125
    year: 1997
    actor: Harrison Ford
    Gary Oldman
    Wendy Crewson
    Glenn Close
    Liesel Matthews
    Paul Guilfoyle (II)
    Xander Berkeley
    William H. Macy
    Dean Stockwell
    Tom Everett
    Jürgen Prochnow
    Donna Bullock
    Wendy Makkena
    Michael Ray Miller
    Carl Weintraub
    director: Wolfgang Petersen
    format: DVD

    For each movie I have multiple actors, and each actor can be in more than one movie. Each movie and will have its own id number. Each actor will also have their own id number. I guess I could set up the actor table like this:

    ID---FNAME---LNAME---MOVIE1---MOVIE2---MOVIE3 ... ---MOVIEX

    and have one movie id per column. But some people may be in only one movie while others may be in 30 or more. So I do not think this would be the best setup.

    How would you suggest that I set up the actor table?

    Thanks,
    Travis

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    create table movies
    ( movieid smallint auto_increment primary key
    , moviename ...
    )

    create table actors
    ( actorid smallint auto_increment primary key
    , actorname ...
    )

    create table movieactors
    ( movieid smallint
    , actorid smallint
    , primary key (movieid, actorid)
    )

    you could also declare the foreign keys, but hey, if mysql nae gonna enforce them, you needn't bother...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •