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 :)