I am trying to solve a low level design problem for car rental application. I have created below models/entities.

public class Car{
    private long id;
    private Type type;
    private String licenseNum;
    private Color color;
    private Location location;
    //other details
    //Color is enum for values RED, WHITE etc
    //TYPE is enum for values SEDAN, SUV, HATCHBACK etc
    //Location is another class that contains location related details
}

public class Booking{
    private long id;
    private Date bookingStartDay;//day of booking
    private int bookingDays;//number of days of the booking, maximum value 50
    private User userId;
    private Location pickUpLoc;
    private Location dropLoc;
    //User is another entity having user details such as id, name, address etc.
}

A user can book a car for upto 50 days. Now, suppose one user is booking a car A from 1st Oct, 2022 for 3 days (i.e till 3rd Oct, 2022). To make sure other users can't book car A for 1st Oct, 2022. I have created a class BookCar which will use carId from entity Car and bookingStartDay from entity Booking as its composite key.

public class BookCar{
    private long carId;
    private Date bookingStartDay;
    //carId and bookingStartDay forms a composite key;
    private User user;
    private BookingStatus status;
    //BookingStatus is an enum with values CANCELLED, RESERVED
}

So, booking status for composite key (carId for A,01-10-2022) will be RESERVED in BookCar entity.

But there is a problem. Car A is booked from 1st Oct, 2022 to 3rd Oct, 2022. But if another user tries to book Car A for 2nd Oct, 2022. It wil shown as available because in BookCar, car A is reserved for (carId for A,01-10-2022) only not for 1st Oct, 2022 to 3rd Oct, 2022.

So, how can I store range of days in my BookCar table. So, that no other user can book the car for reserved days.

I have other questions.

1. Is there any other way to reserve the car for a range of days so that other users can't book it?
2. For car A booking from 1st Oct, 2022 to 3rd Oct, 2022, I add records for (carId for A,01-10-2022), (carId for A,02-10-2022), (carId for A,013-10-2022) in BookCar table. Then, for booking of maximum 50 days for a car, 50 rows will be added in BookCar table or simply 50 write operations will be performed on this table. Is it good a approach to achieve it? I am using MySQL database for this application.

Please try to answer from interview perspective.

CodePudding user response：

In my vieq, we can use just two sql tables for booking. This is an example how classes can be looked based on sql tables:

public class Car {
    private long id;
    private Type type;      
    // other code is omitted for the brevity
}

public class Booking {
    private long id;
    private User userId;
    private long carId;
    private Date bookingStartDay; // day of booking
    private Date bookingFinishDay; // when user should return car
    
    // other code is omitted for the brevity
}

What should we do when user book a car?

Case 1: There is no booking in booking table by carId

We will just insert row in Booking table with date start (column bookingStartDay) and finish date (column bookingFinishDay). So if user book car car_1 from 1 october till 3 october, then we need to insert the following row in Booking table:

Booking:
    id: 1
    userId: 1
    carId: 1
    bookingStartDay: 2022-10-01
    bookingFinishDay: 2022-10-03

We will not add any additional 3 rows in Booking table.

Case 2: If there are bookings in booking table by carId

We are running sql query which will check whether user's desired date is within bookingStartDay and bookingFinishDay.

For example, user want to book a car with id = 1 from 1 October to 3 october, then you can run a query to check whether your RangeFrom and RangeTill are within date range between two dates bookingStartDay and bookingFinishDay:

SELECT * FROM Booking 
WHERE NOT (From_date > @RangeTill OR To_date < @RangeFrom)
    AND carId = 1

Then you can return a message to user whether the car is available to be booked based on result of your query.