4 Replies Latest reply on Apr 20, 2011 7:17 PM by CassW

    Resource Management Database

    CassW

      Title

      Resource Management Database

      Post

      Hi everyone and a pleasure to meet you all. We've got a project in the pipe and we need some assistance to help us crack a particular problem. So here goes.

      We need to set up a capacity management system that can help us track and manage the occupancy of our lodge so that we can be sure we won't overbook them.

      For example, one of our lodges has a maximum capacity of 30 people at any one time and we'd like to have a way to be able monitor the number of people booked into the lodge for overnight stays so we know what is the remaining capacity that we can use. You can think of it as like a hotel booking system where people come and go and the available capacity will be updated as and when people come and go. The database should be able to tell us how much space we have left on a particular day when we query it and warn us if the lodge will be overbooked.

      We're doing this more or less manually now and it takes a bit of extra time to determine the available capacity and mistakes do happen.

      I've been doing FM databases for awhile and although I can 'see' it in my head I just can't put it all together and need some help to move it along. Hope you guys can help.

      Thanks very much!

        • 1. Re: Resource Management Database
          philmodjunk

          You need at least two tables for this: Facilities, Occupancy with this relationship:

          Facilities---<Occupancy

          Facilities::FacilityID = Occupancy::FacilityID AND
          Facilities::DateField > Occupancy::ArrivalDate AND
          Facilities::DateField < Occupancy::DepartureDate

          Occupancy is one record for one group of guests linked to a specific facility.

          This calculation efined in Facilities:

          Sum ( Occupancy::NumberInParty )

          Will compute the total occupancy for a given date you specify in Facilities::DateField.

          You can define a Max Occupancy field in Facilities to compare to this total to check how close to maximum capacity a given facility is for a given date.

          Many variations on this technique are possible depending on the structure of your database and the needs of your business.

          • 2. Re: Resource Management Database
            CassW

            Hi Phil and thanks for your quick response. Much appreciated. I do have a few questions and things I'd like to clarify. So here goes.

            The Occupancy table you mentioned would be something we would call a booking instance I believe. What we have planned is to select a particular facility (lodge) from a pull-down menu list and key in the check in and check out dates there. As we have a several lodges dotted around the place we created a separate table called Lodges with the fields Name, Location and Capacity. This will then be used in a Value List to allow us to choose the lodges that a particular guest would like to stay at.

            The field Facilities::Datefield in your solution sounds like a search field type Global. Yes? So hypothetically if I had a scenario like this. Initial lodge capacity 20 people maximum.

            1st booking: 4 people. Check in 8 April 2011 Check Out 12 April 2011.

            2nd booking: 2 people. Check in 9 APril 2011 Check out 11 April 2011.

            So if I queried the Datefield for 9 April before I made the 2nd booking, it should inform me that there is an existing group present for that day and the remaining capacity is 16. Is that right?

            Additionally, how will the database know to "release" the number of people and add it back to the capacity balance once the check out date is exceeded?

            I hope what I've written here makes some sense and I apologise if it doesn't. But at least now I have a direction to look at. I look forward to hearing from you all on this matter. Thanks very much!

            • 3. Re: Resource Management Database
              philmodjunk

              "The field Facilities::Datefield in your solution sounds like a search field type Global. "

              It can be global but doesn't need to be. If you didn't use a global field and created a series of records for a series of dates for the same facility, you could see the available occupancy at a glance for an entire week or other time period. It's all up to what you want to do here.

              "So if I queried the Datefield for 9 April before I made the 2nd booking, it should inform me that there is an existing group present for that day and the remaining capacity is 16. Is that right?"

              Yes

              How will the database know to "release" the number of people and add it back to the capacity balance once the check out date is exceeded?

              Simply deleting the record that exceedds capacity or changing its date fields to fall on a different date range will automatically update the balance to reflect that change.

              For new bookings where you haven't yet checked occupancy, you might hold the booking data in global fields and generate a new record for this data in the table only after you have confirmed availability. Then there is nothing to "roll back".

              • 4. Re: Resource Management Database
                CassW

                Ok thanks for the help Phil. I'm gonna try and work something out and see if it works. Cool

                And if anyone else have any tips please do share them. Thanks in advance!