Database Design – Booking System with a Twist

database-design

I'm wanting to find a way to how I can implement a booking system that lets users put a start date on the booking and generate the end date whenever.

Lets take a house renting example. Say I wanted to book a house from a particular starting date, but I'm not sure when I'll leave, the maximum stay for users who don't put a end date is 3 days, how could I do build my database so there aren't any booking overlaps?

The current structure I have is:

booking

booking_id      | integer | not null default nextval('booking_booking_id_seq'::regclass)
houseId | integer |
user_id         | integer | not null

booking_date

 booking_date_id | integer                     | not null default nextval('booking_date_booking_date_id_seq'::regclass)
 book_from       | timestamp without time zone | not null
 book_to         | timestamp without time zone | not null
 booking_id      | integer                     |

I'm wondering whether I store an extra field in the booking entity is_stayAndLeaveWhenever

and then in the booking_date I'd just store the book_to with the book_from + 3 days (max stay without choosing leave date)

Is that a solid solution?

Best Answer

the maximum stay for users who don't put a end date is 3 days,

Default end date in db = 3 days. Maybe marker "tentative". Issue being - if you do not know when they move out and can not have overlap, you MUST reserve the maximum stay.

So, your solution is as good as it gets.

Related Solutions

Design for file based with many events

Looking at your table structure means that while the actual row is small (many int values) there will be millions of rows.

Yes you can use a database of your choice, MySQL, Oracle, SQL Server, PostGres for the raw data from the cameras.

You can add partitioning by day which will create a partition for each day, and you may want to consider archiving data on an annual basis into a separate table for each year to make the table sizes manageable.

I would recommend that you setup another set of summary tables for reporting which can be based on the partitions, and this can be done on a daily basis (per partition) and automated.

The actual design of the summary tables depends on the reports you need to run, but it will be necessary so that your queries do not run off the base data table which will be huger.

Postgresql – Want to add a “variable” to a table

Timestamping each row doesn't seem to make a lot of sense to me. I would just keep a log of the events that are interesting. Such a log could be reusable for other events too. E.g. (sorry this is SQL Server dialect, you may have to adjust slightly):

CREATE TABLE dbo.EventLog
(
  EventDate SMALLDATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  [Description] NVARCHAR(2000)
);

Whenever you reload the table, also call a stored procedure that does this:

INSERT dbo.EventLog([Description])
  SELECT N'Re-populated table x';

If you need to know the date of the last refresh, you can say:

SELECT MAX(EventDate) FROM dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

If all you ever care about is the last time, then before the insert above, you could just:

DELETE dbo.EventLog
  WHERE [Description] = N'Re-populated table x';

Related Question