SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database for Movie Ticket Reservation

    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. #2
    Twitter - @CarlBeckel busy's Avatar
    Join Date
    May 2004
    Location
    Richmond, VA, USA
    Posts
    819
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    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. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for replying.What is the format for showDateTime?timestamp or date?
    Can I add one more column Ref Code in reservation table?

  5. #5
    SitePoint Addict
    Join Date
    Aug 2007
    Posts
    256
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    CREATE TABLE Show
    ( movieId INTEGER
    , showDateTime DATETIME
    , PRIMARY KEY ( movieId , showDateTime )
    , FOREIGN KEY ( movieID ) REFERENCES Movies ( id )
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •