final database design questions

[oknow]

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!

[ScallioXTX]

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.

[oknow]

thanks.

how do I implement an index?

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

[r937]

how do I implement an index?

with ALTER TABLE ADD INDEX -- the complete syntax is in da manual

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 --

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)

[oknow]

excellent, thanks!!

[oknow]

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);

[r937]

you would not need the index on userid because the optimizer can use the primary key index

the other one is correct

[oknow]

ah awesome, thanks so much again. appreciate it beyond words.