Database for Movie Ticket Reservation

[devil_vin]

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?Thanks..

[Piranha]

Hello.

I have to say that it is a good description you give, and you have a clear basic database layout. It is not often that I see questions that are this well written. Often a question about this is "Design a database for me to reserve tickets in cinemas", even though it is written in nicer words. Now a few things to think about:

1. You use date and time separately. It would probably be a better idea to use datetime as datatype.

2. Why do you store date and time both in show and reservation? Since the date and time is in show, and you have a foreign key in reservation you don't need that information in reservation.

3. Is the categories "Drama", "drama", "DRAMA" and "darma" the same? The last is a simple typo. The best way to avoid this problem is to use a table for category with a foreign key in movie to that table. You could even have a table between to get a many-to-many relationship, for example if a movie is both "action" and "science fiction".

4. Don't store passwords. Instead store a hash code of the password, for example md5.

5. Maybe address should be more than one field. Street, zip and city could be different columns.

6. What if people want to book a special place to sit?

7. Maybe someone want to reserve more than one ticket? And maybe they want to be sure to sit together if they do?

8. Do the cinema have one or more salons? If it have more you should have a table to mention how many people each takes, and a foreign key in the show table.

Another thing to thnk about is sql injection / database injection. You should read up on that and make sure that you

[devil_vin]

Hello,Thanks for initiative reply...

1,2.Ya.It is what observed as redundancy entities.How about I changed it to following:

Table:Reservation

member_id int(4) PK

show_id int(4) PK

Table:Show

showDateTime PK

movie_id int(4) PK

For showDateTime which format is better?timestamp or datetime?

3.What I supposed for category actually is rating of movie,I still have some entitities like synopsis,language,duration,casting.

4.Maybe I will consider to encrypt password.

5.The address field is not important so I not decided to break it down.

6.Special place?Did you mean like golden class seat?I assume that system used in one cinema with all ordinary theatres.

7.The maximum number of reserved ticket is six.Maybe I will come out a seat diagram to let customer choose seat and store seatNo to reservation table.

8.I will not touch the aspects of payment and ticket issueing, seems like not mandatory for me to control number of people in each salon.How your opinion?

I will add one more column ref_code in reservation table for assigning reference code to customers who successful reserved. So, is it sounds better?

[devil_vin]

Hello,Thanks for initiative reply...

1,2.Ya.It is what observed as redundancy entities.How about I changed it to following:

Table:Reservation

member_id int(4) PK

show_id int(4) PK

Table:Show

showDateTime PK

movie_id int(4) PK

For showDateTime which format is better?timestamp or datetime?

3.What I supposed for category actually is rating of movie,I still have some entitities like synopsis,language,duration,casting.

4.Maybe I will consider to encrypt password.

5.The address field is not important so I not decided to break it down.

6.Special place?Did you mean like golden class seat?I assume that system used in one cinema with all ordinary theatres.

7.The maximum number of reserved ticket is six.Maybe I will come out a seat diagram to let customer choose seat and store seatNo to reservation table.

8.I will not touch the aspects of payment and ticket issueing, seems like not mandatory for me to control number of people in each salon.How your opinion?

I will add one more column ref_code in reservation table for assigning reference code to customers who successful reserved. So, is it sounds better?

[Piranha]

Hi again,

1, 2 seems good, I would use datetime.

4 You should not only consider it. People tend to use the same password everywhere, and without hashed (not encrypted) passwords it is easy to restore the password if you gain access to the database.

6, 7 I ment row 3, place 4. Or row 6, place 16. And it is not possible to see how many tickets are reserved with the database design, at least not in an easy way.

8 So if there are 50 seats in a saloon and there are 60 bookings it is not your problem? That, and what you decide on 7, is the only reasons to know how many seats there are in a saloon.

And don't forget to research sql injection / database injection. It is possible to ruin your database if you don't think about this.