Schema for a reservation system

Question

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?

Answer 1

What I would suggest to you before putting a lot of work into designing a system, is to take a leaf from the book of the great Issac Newton - "If I have seen further it is by standing on the shoulders of giants".

By this I mean take a long hard look at what's been done already - there are systems that could be of interest to you in this domain - take a look at this for example. Of course, in one sense, a taxi is just a resource to be scheduled - so you could look further afield at open source software for scheduling generally for ideas and inspiration.

That's not to say that you can't have your own ideas, but just take a look at some of the contenders in the open source world out there - borrow what you want and need from them and disregard the rest.

With respect to your particular question, I would go to the trouble of maintaining the details of day_off, booked, ill, unavailable... that way you can reward drivers who are never ill, rarely unavailable (i.e. when not actually booked) &c. The more information you can put into the database, the more you can do with it later. Just IMHO.