Set up flexibility for meeting rooms

[TKnTexas]

I am working on a Catering Space database.  I have a table for the rooms.  However, the issue I have is that some rooms may be divisible.  A ballroom could be sold as a ballroom, or since it is divisible into 4 sections.  The subrooms could be booked.  

I have set up an identifier that has a drop down:  

1) Regular (not divisible)

2) Main (a big room that is divisible)

3) Part  (1 segment of the Main) 

One the record for a Part I have a field to tie it to the Main (the fixed auto-assigned number).  

My problem is defining a calculation that is any ONE part is taken, the Main is flagged as Booked.  I could have facilities that have more than one big room.  

I have had a facility with a ballroom that divided into 3 subrooms; 4 smaller rooms that each could be divided in half for a possible total of 8.  

But I could have a facility that has rooms that none subdivide.  There is a table for setting up the rooms.  Then a related table with a record for each room for each day of the year.  These are loaded a year in advance or more.  When looking at the rooms that are AVAILABLE is my issue.  I do not want to risk the ballroom getting booked separate from someone else booking the parts.

[comment]

My problem is defining a calculation that is any ONE part is taken, the Main is flagged as Booked.

 

Where exactly would this calculation live?

[TKnTexas]

I am thinking the calculation would live in the inventory of available rooms.  Changing the status on the sub-room should block the main room.  As I think about this, I am wondering if I need records for each room for each day for each meal period.

[comment]

I am thinking the calculation would live in the inventory of available rooms.

 

But what exactly is "the inventory of available rooms"? The way I see it, you have (only) a table of Rooms and a table of Bookings.

 

Now, since a room can be a child of another room, you should have (at least one) self-join of the Rooms table:

 

Rooms -< SubRooms

 

With this is place, a room can get data about the bookings of its children in a number of ways. For example, you could define a calculation field cRoomIDs (result is Text) =

List ( SubRooms::RoomID )

and use this in a relationship to another occurrence of Bookings, so that:

Rooms::cRoomIDs = Bookings 3::RoomID
and
Rooms::gDate = Bookings 3::Date

I am wondering if I need records for each room for each day for each meal period.

 

That's a good question, and my answer would be no. You should only have records that hold data.

[TKnTexas]

I envisioned one room record for each day of the year.  The data held is AVAILABLE, TENTATIVE, BOOKED

 

Tentative and Booked will link to a booking record of the customer booking and the specifics for the needs of the customer for that day's event.   But then I thought room could have the Knights of Columbus meeting for Breakfast in one Subroom.  Then PetroOil Co might have Subroom1 and Subroom2 for lunch.  Then there is a dinner dance in the complete room.

[comment]

I envisioned one room record for each day of the year.  The data held is AVAILABLE, TENTATIVE, BOOKED

 

That's not an accurate description: the data is not held in this table. The status of AVAILABLE, TENTATIVE or BOOKED is derived from data (or lack thereof) in other tables. This table does not hold any information other than the date - which is not really information at all.

 

I sometimes use a table of "slots" for displaying the status in a calendrical layout - but then the amount of slot records is limited to the maximum number of dates (or time slots) that will be shown at one time (e.g. 42 for a monthly display) and the date of each slot  is calculated from the global field that selects the viewed period. In any case, all this is merely a display device; it plays no role in the data structure.