3

I have following objects: (Rooms) which are (available in a certain period of time [in days]). During the time of availability they can be (booked) by (users). There are basically these four tables. I know that there is a foreign key in (booked) for (users), I also have a foreign key for (rooms) in (booking) and I do have a foreign key of the (room) in (availability). But how can I connect my (availability)-table to avoid that the good is booked on a period of time it isn't available?

The refered question didn't lead to an answer in my matter.

To summarize: (Rooms) - (availability) - (booking) - (User)

I have a hard time figuring out how I set the foreign keys for this transaction.

enter image description here

3
  • possible duplicate of Schema for a lease platform Jul 22, 2015 at 10:38
  • No concise answer to the question has been given over there.
    – empedokles
    Jul 22, 2015 at 10:44
  • @MichaelGreen I see that the 2 questions are related but the question asked there was about billings. Here it's about a different thing. But @emdedokles, please use room and not good. The id_room is confusing (or change it to id_good.) Jul 22, 2015 at 12:58

2 Answers 2

0

The most relational way is adding a day table. You will be able to join room, availability and day to find out which rooms are available for a certain period.

3
  • I already have date fields (from and till) in the availability table. Why isn't this enough?
    – empedokles
    Jul 22, 2015 at 15:42
  • How do you find out if a room is available for a specific date? Performing a BETWEEN is less efficient. Also, with the relational solution you can ask questions like "How much days the room 212 was reserved, last year?" Jul 23, 2015 at 8:59
  • I thought about doing a if transaction.check_in is > availability.from and transaction.check_out is < availability.till it would allow a reservation. I don't quite understand what you are proposing. Can you draw it in a simplified version? What fields does your table day contain?
    – empedokles
    Jul 23, 2015 at 9:24
0

(Rooms) - (availability) - (booking) - (User)

room:

room_id
description
address

user:

user_id
name
phone

room_availability:

availability_id
room_id: room_id of room table [relationship]
start_dt
end_dt
price
is_available

This table has all the availability information for each room. Note the is_available flag. Also, there need check that for a room the set of start_dt and end_dt values do not overlap for a room's availability. For example, some available rooms:

  • room_1 - 24-Dec-2023 and 28-Dec-2023
  • room_1 - 31-Dec-2023 and 31-Dec-2023
  • room_1 - 30-Dec-2023 and 2-Jan-2024 <--- this data is not valid

booking_transaction:

booking_id
booked_by: user_id of user table [relationship]
availability_id: availability_id of room_availability table [relationship]
booked_from
booked_to
amount
payment_details

Now, having a unique constraint on the booking_transaction.availability_id will ensure that a room that is booked for a set of available dates (i.e., room availability) cannot be booked again. Also, the is_available flag can tell if the room is available or not. This can address the issue asked in the question post:

But how can I connect my (availability)-table to avoid that the good is booked on a period of time it isn't available?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.