CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-designdatabase-schema
Anton
Anton

Reputation: 4403

Reservation system design

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?

Upvotes: 1

Views: 1644

Answers (1)

Low Flying Pelican
Low Flying Pelican

Reputation: 6054

The easier way is to avoid the driver availability (because he is off / taxi is in repair) is to add a boolean property in the Drivers table which can be turned on or turned off from the UI.

And Reservation should have a property as cancelled, so that when querying for available drivers cancelled reservations can be ignored.

So the available drivers would be

select *
from drivers d
where d.Available = true
and not exists (
  select top(1) r.id
  from rerservations r
  where r.driverid = d.id
  and r.cancelled = false
  and r.start < GetDate()
  and r.end > GetDate()
)

Upvotes: 1

Related Questions