AnsweredAssumed Answered

Database layout - filtered relationships?

Question asked by EmanuelKarlsson on Jun 19, 2011
Latest reply on Jun 19, 2011 by EmanuelKarlsson


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