I need to develop a mobile application in which the users will be able to visualize the reservation schedule of the current week (which hours are taken and which are available). My approach was to have the following table:
CREATE TABLE Reservations (
Id serial NOT NULL,
BookDate timestamp NOT NULL,
SessionDate timestamp NOT NULL,
FinishDate NOT NULL,
User_Id int NOT NULL,
Status varchar(30) NOT NULL,
CONSTRAINT Reservation_pk PRIMARY KEY (Id)
);
From there, my backend will identify the week we are currently at, and query all the reservations between this week's monday and sunday. This would later be displayed by the mobile app in a nicely manner.
The issue is that i realized the reservations are ever-growing and the only thing the mobile app needs to display is the current week. I don't want to query from say 3 million rows and do all those filters (to see if the reservation has been canceled or if it is from a past date) only to display the current week.
So i thought that maybe i could have a reservation table that only handles the current week reservations, and after the week ends then the row gets deleted from there and goes to a reservation history table (which would have the same structure).
Currently, this seems like a good solution to me, but i don't know if people with higher database design skills would consider this a great practice. I'd be thankful to hear any recommendations or comments regarding this requirement or my aproach.
One table is the way to go until you discover otherwise. Just get any calling application or process to query the days needed for the task at hand. You can add indexes on the various dates to make that more efficient. If you really, really run into issue you can add a date partition, but that just swaps one set of inefficiencies for another so you need to understand what you are doing.