Hotel reservation system with discounts and deposits

I'm making a hotel reservation app that has the following features:

Promo Codes:

The guest can choose to pay half a certain percent of the total_amount or the entire total_amount. I want to use stripe, PayPal and authorize.net as payment gateways.

Reservations are stored here:

CREATE TABLE `reservations` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `user_id` int(11) NOT NULL,
 `guest_id` int(11) NOT NULL,
 `currency` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'USD',
 `accommodation_id` int(11) NOT NULL,
 `from_date` date NOT NULL,
 `to_date` date NOT NULL,
 `guests` int(11) NOT NULL,
 `total_amount` decimal(13,4) NOT NULL,
 `deposit_amount` decimal(13,4) NOT NULL,
 `remaining_amount` decimal(13,4) NOT NULL,
 `isDeposit` enum('true','false') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'false',
 `promo_id` int(11) NOT NULL,
 `paid` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

An example reservation:

id|user_id  | guest_id  | currency  | accommodation_id  | from_date  | to_date        | guests  | total_amount  | deposit_amount | remaining_amount 
2 | 4       | 10        | USD       | 55                | 2015-07-02 | 2015-07-04     | 20      | 200           | 20             | 180              

| isDeposit | promo_id | paid 
| true      | 2        | 0

Payments are stored here:

CREATE TABLE `payments` (
 `id` int(11) NOT NULL,
 `reservation_id` int(11) NOT NULL,
 `method` enum('stripe','paypal','authorize') NOT NULL,
 `type` enum('deposit','total','remaining') NOT NULL,
 `reference` varchar(255) NOT NULL,
) ENGINE=InnoDB DEFAULT CHARSET=latin1

If the guest pays that deposit_amount of 20 using stripe:

id | reservation_id | method | type     |  reference        |
1  | 2              | stripe | deposit  | 56544564545       |

and pays the remaining of 180 using PayPal:

```
id | reservation_id | method | type | refere

Not a full & detailed answer but some initial thoughts.

Some of the fields in the reservations table look decidedly non-normalised - remaining_amount, isDeposit and paid. Shouldn't these be derived from the payments table when needed for a view or a query? Otherwise, when you receive a payment you would also have to update at least two of these fields. Are you planning on using triggers for these updates?

If you want to keep the isDeposit field, then think carefully how you use it. Does it tell you that only a deposit has been paid so it gets set to 0 once the full amount is paid? Or does it tell you that the guest initially paid a deposit so it gets set to 1 when the deposit is paid and then gets left?

What happens when a reservation gets cancelled? There is no status field and presumably you cannot delete the record (and any payments records)?

You've also got user_id, guest_id and guests fields. If you want the user to enter names of any guests for the reservation you will need another structure.

StackExchange Code Review Q#96476, answer score: 3