title: Hotel Management Database Design Problems
body: <p>I am trying to get started with Web Development by trying to develop a Hotel Management System.</p>
  
  <p>I am using MySQL 5.5.44 (Raspberry Pi Debian) and PHP PDOs for the Communication.</p>
  
  <p>The Foreign Keys and Primary Keys are confusing the living hell out of me.</p>
  
  <p>This is my DB Design (Graph created via DbVisualizer)</p>
  
  <p><a href="https://i.stack.imgur.com/i4GH3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/i4GH3.png" alt="HotelDB"></a></p>
  
  <p>The Problems are:</p>
  
  <p>I have an Option in the Members Area of the Website where a User can Cancel a Reservation. To do so I want to copy the cancelled Booking (Row in the Bookings Table) into the Cancellation Table and that works BUT after doing that i want to delete the cancelled Booking from Bookings Table. Now this is where my Dilemma starts. I cannot delete a Parent Row from the Bookings Table as the Booking ID in the Cancellation Table AND the Payments Table are connected via References (FK and PK).</p>
  
  <p>I need the BookingID to be the PK as it is the only unique value in the entire System. A Customer might have a unique ID but he can have multiple Bookings and a PK must be unique in the Table. Multiple Bookings per Customer allow the same CustomerID to be present multiple times in the Table. But a BookingID is always unique, even if it is the same Customer. That is why i need to have the Booking ID.</p>
  
  <p>How do i get this to work?</p>
  
  <p>How can I keep BookingID as FK and PK in the Payments and Cancellation Tables but still get rid of the row in the Bookings Table?</p>
  
  <p>Do i have to redesign the DB-Schema? If Yes, how would you do it?</p>
  
  <p>Here are the <code>DESCRIBE</code>s of the Tables:</p>
  
  <pre><code>mysql&gt; describe Bookings;
  +------------+--------------+------+-----+---------+-------+
  | Field      | Type         | Null | Key | Default | Extra |
  +------------+--------------+------+-----+---------+-------+
  | BookingID  | int(11)      | NO   | PRI | 0       |       |
  | Arrival    | datetime     | YES  |     | NULL    |       |
  | Checkout   | datetime     | YES  |     | NULL    |       |
  | RoomNumber | int(11)      | YES  | MUL | NULL    |       |
  | CustomerID | int(11)      | YES  | MUL | NULL    |       |
  | Breakfast  | int(11)      | YES  |     | NULL    |       |
  | Nights     | int(11)      | YES  |     | NULL    |       |
  | Comment    | varchar(400) | YES  |     | NULL    |       |
  | BookType   | varchar(50)  | YES  | MUL | NULL    |       |
  | BookTime   | datetime     | YES  |     | NULL    |       |
  +------------+--------------+------+-----+---------+-------+
  10 rows in set (0.00 sec)
  
  
  mysql&gt; describe Cancellations;
  +------------+----------+------+-----+---------+-------+
  | Field      | Type     | Null | Key | Default | Extra |
  +------------+----------+------+-----+---------+-------+
  | BookingID  | int(11)  | NO   | PRI | 0       |       |
  | Arrival    | datetime | YES  |     | NULL    |       |
  | Checkout   | datetime | YES  |     | NULL    |       |
  | RoomNumber | int(11)  | YES  | MUL | NULL    |       |
  | CustomerID | int(11)  | YES  | MUL | NULL    |       |
  | Breakfast  | int(11)  | YES  |     | NULL    |       |
  | Nights     | int(11)  | YES  |     | NULL    |       |
  | BookTime   | datetime | YES  |     | NULL    |       |
  | CancelTime | datetime | YES  |     | NULL    |       |
  +------------+----------+------+-----+---------+-------+
  9 rows in set (0.00 sec)
  
  mysql&gt; describe Payments;
  +------------+---------------+------+-----+---------+-------+
  | Field      | Type          | Null | Key | Default | Extra |
  +------------+---------------+------+-----+---------+-------+
  | BookingID  | int(11)       | NO   | PRI | 0       |       |
  | CustomerID | int(11)       | YES  | MUL | NULL    |       |
  | Amount     | decimal(10,0) | YES  |     | NULL    |       |
  | Paid       | varchar(10)   | YES  |     | NULL    |       |
  | PayTime    | datetime      | YES  |     | NULL    |       |
  | Invoice    | varchar(50)   | YES  |     | NULL    |       |
  | Cancelled  | varchar(10)   | YES  |     | NULL    |       |
  +------------+---------------+------+-----+---------+-------+
  7 rows in set (0.00 sec)
  </code></pre>
  
  <p>I hope you guys can help a Beginner with his ambitious Project.</p>
  
  <p>Thank You.</p>