Zombievirus Zombievirus - 6 months ago 49
SQL Question

Hotel Management Database Design Problems

I am trying to get started with Web Development by trying to develop a Hotel Management System.

I am using MySQL 5.5.44 (Raspberry Pi Debian) and PHP PDOs for the Communication.

The Foreign Keys and Primary Keys are confusing the living hell out of me.

This is my DB Design (Graph created via DbVisualizer)

HotelDB

The Problems are:

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).

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.

How do i get this to work?

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?

Do i have to redesign the DB-Schema? If Yes, how would you do it?

Here are the

DESCRIBE
s of the Tables:

mysql> 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> 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> 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)


I hope you guys can help a Beginner with his ambitious Project.

Thank You.

Answer

Add a is_cancelled Boolean column to the bookings table and drop the cancellations table completely.

Comments