Hotel Management Database Design Problems
<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>