0

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?

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.

  • Opencab seems to be a dead project. At least I can't find their source anywhere. But thank you for the suggestion. I'll try to find some inspiration – Anton Sep 26 '14 at 0:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.