A Car Reservation System is a very popular question in Software Development Interviews. I designed the following ER structure/OOP structure when I was asked this:
Car class/table has Car Id#, Type (Compact, Standard, etc), Make, Model Customer has Customer Id#, etc Reservation is an associative table storing Car Id#, Customer Id# and Date for which the car is reserved. For the sake of simplicity we can assume that car can be reserved only in chunks of days.
Here's the tricky part where I floundered - What happens when a customer 1 reserves a compact car having Id Car 3 on July 27. What happens if Car 3 is totaled on July 24. Other compact cars are available, however between July 24 and 27 they all get taken. So when Customer 1 arrives on July 27, he ultimately has no car.
The flaw I think is in the way Car and Customer are coupled in the Reservations table. Is there any better way to model this relationship and also the regular use cases such find an available car for a particular date, reserve it for that date etc.