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

  • possible duplicate of Schema for a lease platform – Michael Green Jul 22 '15 at 10:38
  • No concise answer to the question has been given over there. – empedokles Jul 22 '15 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.) – ypercubeᵀᴹ Jul 22 '15 at 12:58

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.

| improve this answer | |
  • I already have date fields (from and till) in the availability table. Why isn't this enough? – empedokles Jul 22 '15 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?" – Federico Razzoli Jul 23 '15 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 '15 at 9:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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