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.