1 Reply Latest reply on Jun 19, 2011 2:31 PM by EmanuelKarlsson

    Database layout - filtered relationships?



      Database layout - filtered relationships?



      I've been banging my head in my desk for the last couple of days now because I can't come up with a reasonable solution for this problem.

      I'm updating my companys database (tour operator), it looks like this:

      [room types]>--[trips]--<[bookings]--<[room booking]

      room types, key roomID, contains ie. 17, double-room, 6, single

      trips, key tripID, length of trip, destination etc..

      bookings, keys bookingID, tripID, name, adress, cancelled, etc

      room booking, keys roomID, tripID, ammount of rooms

      The problem is I want the know how many avalible rooms there are left. It's easy to do [room types]--<[room booking]. But then I don't get the information if a booking is canceled or not. When a booking is canceled I naturally want to free that room. But this information is in the bookings table wich gets lost if I link [room types]--<[room booking].

      in sql it would be (sort of):

      select * from room_types, room_booking, bookings where room_types.roomID = room_booking.roomID and bookings.bookingID = room_booking.bookingID and bookings.canceled = FALSE

      Any suggestions would be highly appreciated :)