title: Movie booking: How to let every show have its own set of seats?
body: <p>I have made a movie booking database in mysql workbench (6.2). This database is then connected to eclipse where I in java have programmed a GUI for the movie booking system. </p>
  
  <p>Everything works well with the GUI/database but there is one logical problem:
  If a reservation is made for a show, naturally, the number of seats left in that theater where that show is to be played, will be reduced by 1 for every reservation. But if there is a show that's on a <strong>different</strong> date but on the <strong>same</strong> theater then this theaters seats is also reduced by 1 which is wrong.</p>
  
  <p>E.g. a show on <em>Monday</em> should have its own set of seats and one on <em>Tuesday</em> should have its own. A solution could be to create a new column 'nbrseats' in 'shows' but I want it to get its seats from Theaters if that's possible.</p>
  
  <p>Database</p>
  
  <pre><code>set foreign_key_checks = 0;
  drop table if exists Users;
  drop table if exists Theaters;
  drop table if exists Movies;
  drop table if exists reservations;
  drop table if exists Shows;
  
  create table Theaters ( 
      theatername char(11) not null,
      NbrSeats char(20) not null,
      primary key (theatername)
  )engine innoDB;
  
  create table Movies ( 
      moviename char(30) not null,
      primary key (moviename)
  )engine innoDB;
  
  create table Shows (   
      movieDate DATE not null,  
      theatername char(11) not null,  
      moviename char(30) not null,
      id integer auto_increment,
  
      foreign key (theatername) references Theaters(theatername),  
      foreign key (moviename) references Movies(moviename),
      primary key (id) 
  )engine innoDB;
  
  create table Users ( 
      username char(20) not null,
      fullname char(30) not null,
      phonenbr char(10) not null,
      address varchar(20) not null,
  
      primary key (username)
  ) engine innoDB;
  
  
  create table reservations (
      resNbr integer auto_increment,
      username char(20) not null,
      showId int(30) not null,
  
      foreign key(showid) references Shows(id),
      foreign key (username) references Users(username),
      primary key (resNbr)
  )engine innoDB;
  
  -- insert data into the tables
  insert into Users values
  ('Temp1','Name Name', '0701231231', 'Street1');
  
  insert into Movies values
  ('Star Wars'),
  ('Dallas'),
  ('Falcon Crest');
  
  insert into Theaters values
  ('Filmstaden', '100'),
  ('SF', '129'),
  ('Royal', '120');
  
  insert into Shows values
  ('20151203','Royal', 'Falcon Crest', null),
  ('20151003','SF', 'Dallas', null),
  ('20150803','Filmstaden', 'Star Wars', null);
  </code></pre>
  
  <p>Thanks!</p>