HOW TO UPDATE BED NUMBER AND RENT PAR AMOUNT AUTO
- Thread starter moin555
- Start date
The relationships between your tables are nonsensical - every table is related to every other table and at least some of the attributes appear poorly assigned. Think through each of the tables as concepts:
ROOMS: Presuming these are the items that are normally "rented" or booked, not beds. But ignore for the moment
One or more Rooms are located on FLOORS (of a Property)
A room has one or more BEDS
If this is the "picture" of how these concepts relate to each other then
FLOOR is 1:N with ROOMS
ROOM is 1:N with BEDS
FLOOR: FloorID, FloorName
ROOM: RoomID, FK_FLOORID, RoomNo, RoomStatus, (maxOccupancy)
BED: BedID, FK_ROOMID, BedType (which could be supported by a BedType table), BedStatus (if this is not about occupancy then room status seems more appropriate)
Consider now how booking take place - are bookings made against a room or a bed? That will tell you where to place the RentalCostperNight. Note however, that if it is by BED then are you allowing guests to share a room, being allocated a bed each for different periods of time? Or if it is only a bed rental - it is assumed only one person per bed?
BOOKING: Booking ID, FK_GuestID, GuestCount, CheckinDate, CheckoutDate, FK_ROOMID or FK_BEDID (depending on your answer to above question)
A table to hold guest details would seem appropriate)
The FK to room in the booking table allows you to determine which room is booked (and the floor the room is on) for which guest (and the no of guests) on which dates and how much will be charged (assuming the rate stays constant for the room irrespective of date)
when you have the tables straightened out you can more easily determine the rental as a simple multiplication
the calculation given by arnel returns the rental per night - it does not take account of the period of rental
ROOMS: Presuming these are the items that are normally "rented" or booked, not beds. But ignore for the moment
One or more Rooms are located on FLOORS (of a Property)
A room has one or more BEDS
If this is the "picture" of how these concepts relate to each other then
FLOOR is 1:N with ROOMS
ROOM is 1:N with BEDS
FLOOR: FloorID, FloorName
ROOM: RoomID, FK_FLOORID, RoomNo, RoomStatus, (maxOccupancy)
BED: BedID, FK_ROOMID, BedType (which could be supported by a BedType table), BedStatus (if this is not about occupancy then room status seems more appropriate)
Consider now how booking take place - are bookings made against a room or a bed? That will tell you where to place the RentalCostperNight. Note however, that if it is by BED then are you allowing guests to share a room, being allocated a bed each for different periods of time? Or if it is only a bed rental - it is assumed only one person per bed?
BOOKING: Booking ID, FK_GuestID, GuestCount, CheckinDate, CheckoutDate, FK_ROOMID or FK_BEDID (depending on your answer to above question)
A table to hold guest details would seem appropriate)
The FK to room in the booking table allows you to determine which room is booked (and the floor the room is on) for which guest (and the no of guests) on which dates and how much will be charged (assuming the rate stays constant for the room irrespective of date)
when you have the tables straightened out you can more easily determine the rental as a simple multiplication
the calculation given by arnel returns the rental per night - it does not take account of the period of rental
When I select the First Floor, only the bedrooms of the First Floor should be shown. How can that be done?
Eugene-LS
Registered User.
- Local time
- Today, 15:53
- Joined
- Dec 7, 2018
- Messages
- 519
CAN YOU HAVE ANY DEMO DATA BASE FOR THIS TYPE ?
GPGeorge
George Hepworth
- Local time
- Today, 05:53
- Joined
- Nov 25, 2004
- Messages
- 2,961
YouTube has a built in search feature you can use to locate potentially useful videos.
Enter the appropriate search term, such as "Cascading Combo Boxes", which were mentioned in this thread. The key is to be an active participant in the search.
