Starting php/mysql site, need advice

Databases
1

hi guys, just read through ‘build your own database…’ book and admittedly got a little overwhelmed around chapter 7.

so, purely for my own practice and to try and hammer this stuff into my brain, I am going to make a little private site that allows users to register and make lists of movies they own.

when they register I want their own directory created i.e site.com/user/ and that will be their homepage to show information about the movies they own.

but to get me started I am trying to figure out the tables I will need to get me started.

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

so that would be the table to store my userbase in - does it look ok for that purpose? I want them to be able to login with either: user+pass or email+pass.

where I’m really having trouble is figuring out what kind of table I would use to store a list of movies.

I want to have a pre-made list of movies that users will be able to select from and add to their ‘owned’ list that will be shown on their personal page.

so how to make that table of pre-made movies? hmm.

CREATE TABLE movies (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
format VARCHAR(255),
movie VARCHAR(255),
genre VARCHAR(255),
region VARCHAR(255)
) DEFAULT CHARACTER SET utf8;

does anyone have any tips regarding that one? I’m probably missing something regarding the format of the movie table, for the purposes of ease-of-linking to other tables etc… but that’s what I want advice on…

thanks for any help

2

your movies table looks fine

carry on

3

You would want to have a “joining table” called something like movie_ownership recording in each row a user id and the id of a movie.

The region field only needs to be 1 character long if it is refering to the regions that DVD releases are split up into.

4

ok thank you very much guys.

I will also re-read the section in the book that goes over joining.