Database for Movie Ticket Reservation

Databases
1

Hi…guys! I am newbie to MySQL,now doing a online movie ticket reservation system.Is my database structure feasible to work out the reservation process?

Table:Member
id int(4) primary key auto-increment
name varchar(50)
email varchar(50)
password varchar(15)
telephone char(10)
address varchar(70)

Table:Movie
id int(4) primary key auto-increment
name varchar(100)
category char(4)

Table:Reservation
id int(4) primary key auto-increment
reservedDate date (screenning date of movie)
reservedTime time (screeening time of movie)
member_id int(4) foreign key
show_id int(4) foreign key

Table:Show
id int(4) primary key auto-increment
showTime time (screenning date of movie)
showDate date (screeening time of movie)
movieId int(4) foreign key

This system only apply in one cinema,the showTime and showDate are basically refered as the “timetable” of each movie because I want it to be fetch from database and display in proper order.The reservedDate and reservedTime are the movie date and time issued in ticket.Is there any redundancy entity?

2 

Table:Reservation
id int(4) primary key auto-increment
member_id int(4) foreign key
show_id int(4) foreign key

You can get the show times from table “Show”. Don’t need to add them to two tables.

3

remove first three columns of Reservation (they are redundant and/or not needed), make the remaining two a composite PK

remove first three columns of Show, add back showDateTime (one column, not two), make movieID and showDateTime a composite PK

4

Thanks for replying.What is the format for showDateTime?timestamp or date?
Can I add one more column Ref Code in reservation table?

5

Is it neccessary to defined those id as auto-increment?For composite PK only one column can be defined as auto-increment,which is better?

6

CREATE TABLE Show
( movieId INTEGER
, showDateTime DATETIME
, PRIMARY KEY ( movieId , showDateTime )
, FOREIGN KEY ( movieID ) REFERENCES Movies ( id )
)