I'm working on a reservation system schema and have some problems defining availability of resources. I searched for similar question here and on Google but still don't have clear understanding
My app is for booking taxis. Where each taxi driver is either working today or taking a day off. So far I have come up with the following structure
table: Drivers id INT PRIMARY KEY name INT table: Users id INT PRIMARY KEY name TEXT table: Reservations id INT PRIMARY KEY start: DATETIME end: DATETIME userID: INT (FOREIGN KEY(Users.id)) driverID: INT (FOREIGN KEY(Drivers.id))
I was thinking of treating each driver as available if there is no reservation entry, and then coming up with different types of reservations:
Unavailable => driver is taking a day off Booked => driver is booked by a user between start/end times Cancelled => cancelled booking
However, maintaining Unavailable/Booked status sounds a bit more complicated than I'd like to.
So, are there any suggestions how to improve the database schema?