SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    final database design questions

    Hi, just after some advice on the best way to go about the final part of my db design.

    at the moment I have a movies table:

    movies (
    'movie_id' INT,
    'movie_name' VARCHAR(255),
    'movie_genreid' INT,
    'movie_region' CHAR(3),
    'release_date' DATE
    ) DEFAULT CHARSET utf8

    and a users table:

    users (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(55) UNIQUE,
    email VARCHAR(55) UNIQUE,
    password CHAR(32)
    ) DEFAULT CHARACTER SET utf8

    I have thousands of movies in the movies table and I have an addmovie.php form that allows users to search for the movies.

    I want users to be able to add movies from their searches to their own user page (users.php?user=username).

    I'm just wondering on the best database design for this.. should I just make a 'usermovies' table to link to the movies table? and then just add every users selected movies to the usermovies table? ie

    userid - movieid
    1 - 343
    1 - 4656
    7 - 234
    1 - 111
    2 - 3434

    any tips would be wonderful.

    thanks!

  2. #2
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,067
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    your suggestion sounds right. As far as I know there is no other (good) way to do it.
    You want to put an index on the userId column to allow for faster loading of the user pages.
    And of course the combination userId, movieId should be UNIQUE.

  3. #3
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    thanks.

    how do I implement an index?

    and also the UNIQUE combination userID, movieID.. or is that done in the query?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by oknow View Post
    how do I implement an index?
    with ALTER TABLE ADD INDEX -- the complete syntax is in da manual

    Quote Originally Posted by oknow View Post
    and also the UNIQUE combination userID, movieID.. or is that done in the query?
    no, that is best done by making them the primary key --
    Code:
    CREATE TABLE usermovies
    ( userid INTEGER NOT NULL 
    , movieid INTEGER NOT NULL 
    , PRIMARY KEY ( userid, movieid )
    );
    a primary key is unique by definition

    if you ever need to search which users have a particular movie, you will also need an index on (movieid,userid)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    excellent, thanks!!

  6. #6
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    with ALTER TABLE ADD INDEX -- the complete syntax is in da manual
    Just got done reading the manual page.. so to create the indexes I need would require the following syntax?

    ALTER TABLE usermovies ADD INDEX (userid);
    ALTER TABLE usermovies ADD INDEX (movieid, userid);

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,248
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you would not need the index on userid because the optimizer can use the primary key index

    the other one is correct

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Zealot
    Join Date
    Jan 2010
    Posts
    140
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    ah awesome, thanks so much again. appreciate it beyond words.


Bookmarks

Posting Permissions

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