Database Management For Key Duplicate Issue
- December 21, 2023/ Uncategorized
Managing Key Duplicates in bookings Table
Database tables
MariaDB [hrm]> describe bookings;
+————–+———–+——+—–+———————+—————————–+
| Field | Type | Null | Key | Default | Extra |
+————–+———–+——+—–+———————+—————————–+
| BookingID | int(11) | NO | PRI | NULL | |
| GuestId | int(11) | NO | MUL | NULL | |
| ROOMNUM | int(11) | NO | MUL | NULL | |
| CheckInDate | date | NO | | NULL | |
| CheckInTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| CheckOutDate | date | NO | | NULL | |
| CheckOutTime | timestamp | NO | | 0000-00-00 00:00:00 | |
| NUMDAYS | int(11) | NO | | NULL | |
| INVOICENUM | int(7) | NO | | NULL | |
| INVOICEDATE | date | NO | | NULL | |
+————–+———–+——+—–+———————+——–
MariaDB [hrm]> describe country_lookup;
+———–+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———–+————-+——+—–+———+——-+
| COUNTRYID | int(4) | NO | PRI | NULL | |
| COUNTRY | varchar(20) | NO | | NULL | |
+———–+————-+——+—–+———+——-+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe facility_list;
+————+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+———-+——+—–+———+——-+
| FacilityID | int(4) | NO | PRI | NULL | |
| Facility | char(10) | NO | | NULL | |
+————+———-+——+—–+———+——-+
2 rows in set (0.02 sec)
MariaDB [hrm]> describe guests;
+—————-+————–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————-+————–+——+—–+———+——-+
| guestID | int(4) | NO | PRI | NULL | |
| title | char(10) | NO | | NULL | |
| GFirstName | char(20) | NO | | NULL | |
| GLastNAME | char(20) | NO | | NULL | |
| Address | varchar(255) | NO | | NULL | |
| Suburb | char(30) | NO | | NULL | |
| State | char(3) | NO | | NULL | |
| PostCode | int(4) | NO | | NULL | |
| Country | int(11) | NO | | NULL | |
| Mobile | int(9) | NO | | NULL | |
| PhoneOther | int(9) | NO | | NULL | |
| emailID | varchar(255) | NO | | NULL | |
| Licence_No | varchar(10) | NO | | NULL | |
| PassportNumber | int(9) | NO | | NULL | |
| Note | varchar(255) | NO | | NULL | |
| DATE_ENTERED | date | NO | | NULL | |
+—————-+————–+——+—–+———+——-+
MariaDB [hrm]> describe notes
-> ;
+————–+————–+——+—–+——————-+—————————–+
| Field | Type | Null | Key | Default | Extra |
+————–+————–+——+—–+——————-+—————————–+
| Note_ID | int(4) | NO | PRI | NULL | |
| GuestId | int(11) | NO | MUL | NULL | |
| Entrydate | date | NO | | NULL | |
| EntryTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
Managing Key Duplicates in country_lookup Table
| Initials | char(2) | NO | | NULL | |
| Assigned_to | int(11) | YES | MUL | NULL | |
| Remarks | varchar(255) | NO | | NULL | |
| FollowupDate | int(11) | NO | | NULL | |
| Complete | int(11) | NO | | NULL | |
+————–+————–+——+—–+——————-+—————————–+
9 rows in set (0.02 sec)
MariaDB [hrm]> describe payments;
+——————-+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+—————+——+—–+———+——-+
| PaymentID | int(11) | NO | PRI | NULL | |
| BookingID | int(11) | NO | MUL | NULL | |
| PaymentAmount | decimal(10,0) | NO | | NULL | |
| PaymentDate | date | NO | | NULL | |
| PaymentMethod | int(11) | NO | MUL | NULL | |
| CheckNumber | int(11) | YES | | NULL | |
| CreditCardType | int(11) | YES | | NULL | |
| CreditCardNumber | int(11) | YES | | NULL | |
| CreditCardNAME | char(20) | YES | | NULL | |
| CreditCardExpDate | date | YES | | NULL | |
| PAYMENT_TERMS | varchar(255) | YES | | NULL | |
| Note | varchar(255) | YES | | NULL | |
+——————-+—————+——+—–+———+——-+
12 rows in set (0.02 sec)
MariaDB [hrm]> describe payment_methods;
+—————–+————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+—————–+————+——+—–+———+——-+
| PaymentMethodID | int(11) | NO | PRI | NULL | |
| PAYMENTMETHOD | char(10) | NO | | NULL | |
| CREDITCARD | tinyint(1) | NO | | NULL | |
+—————–+————+——+—–+———+——-+
3 rows in set (0.02 sec)
MariaDB [hrm]> describe reservations;
+———————–+————+——+—–+———————+—————————–+
| Field | Type | Null | Key | Default | Extra |
+———————–+————+——+—–+———————+—————————–+
| BookingId | int(11) | NO | PRI | NULL | |
| GuestId | int(11) | NO | MUL | NULL | |
| BookingDate | date | NO | | NULL | |
| BookingTime | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| NumAdults | int(11) | NO | | NULL | |
| NumChildren | int(11) | NO | | NULL | |
| ExpectedArrivalDate | date | NO | | NULL | |
| ExpectedArrivalTime | timestamp | NO | | 0000-00-00 00:00:00 | |
| ExpectedDepartureDate | date | NO | | NULL | |
| ExpectedDepartureTime | timestamp | NO | | 0000-00-00 00:00:00 | |
| VIP | tinyint(1) | NO | | NULL | |
| NOTES | int(11) | YES | | NULL | |
| ROOMNUM | int(11) | NO | MUL | NULL | |
| Confirmed | tinyint(1) | NO | | NULL | |
| Cancelled | tinyint(1) | NO | | NULL | |
+———————–+————+——+—–+———————+—————————–+
15 rows in set (0.02 sec)
MariaDB [hrm]> describe rooms;
+———–+—————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———–+—————+——+—–+———+——-+
| RoomId | int(11) | NO | PRI | NULL | |
| ROOMNUM | int(11) | NO | UNI | NULL | |
| RATE | decimal(10,0) | NO | | NULL | |
| NUMBEDS | int(11) | NO | | NULL | |
| VIEW | tinyint(1) | NO | | NULL | |
| QUIET | tinyint(1) | NO | | NULL | |
| SUITE | tinyint(1) | NO | | NULL | |
| DELUXE | tinyint(1) | NO | | NULL | |
| ROOM_TYPE | int(11) | NO | | NULL | |
+———–+—————+——+—–+———+——-+
9 rows in set (0.02 sec)
MariaDB [hrm]> describe room_facility;
+————+——–+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————+——–+——+—–+———+——-+
| RoomID | int(4) | NO | PRI | NULL | |
| FacilityID | int(4) | NO | PRI | NULL |
16 rows in set (0.02 sec)
During the course of development, I faced various challenges. First was the set up of XAMPP and make phpMyAdmin working due to some username issue. I resolved it by uninstalling and then installing a 32-bit xampp on my windows machine. Next, challenge was the data import in the database through CSV file. Crating files in CSV format was still an easy task but there were some rows present at the beginning of each sheet describing the table structure. Whenever, I imported CSV file in the database, it showed me CSV column mismatch error. Then, I tried deleting those rows and then importing the CSV file. Then it worked perfectly. Critical issue I faced and was not able to resolve was the complete importing of Service_charges data. I created the correct CSV file, but whenever I tried importing it, it gave me primary key duplicate issue. I even made (bookingID, serviced) as primary key but there were still some shows having duplicate entry for this combination. Also, I thought of including invoicenum in this primary key but there were some entries which are having same values for all three columns. But that also, it didn’t work. This was the only data which was not loaded successfully.
Next unresolved issue was feeding correct timestamp in the tables like in bookings table. I formatted the data before importing but still the database tables like booking have 0000-00-00 as an entry for all TIMESTAMP based values.
