Room hiring service design

Giving a small glance into one facet of the work which I perform, I was approached to design a database for a room hiring service, to be implemented in Priority. At first glance, this appears to be the same as a hotel management system: there are rooms which are booked from date until date, each room with its own price. This is fairly standard, although the date handling can be tricky. But what makes this system stand out is the fact that whilst some rooms are 'private', other rooms are 'communal' - a big room could hold fifty workstations, where someone can hire a workstation for a few days. Thus an order for such a workstation would leave the room 'vacant', and other people can hire a workstation in the same room for the same dates. A field will need to be added to the 'parts' table to represent this (1 would indicate 'private', whereas any other value would indicate the number of workstations in the room).

Based on the constraint that the implementation will be in Priority (which simplifies certain things but forces one to work within the framework of Priority), my first decision is that 'each room is a part'. This allows us to use the standard 'orders' screen in Priority, along with the 'order items' screen. Let's see what functionality exists, what has to be added, and where there are problems.

Assuming that there is a part with catalogue number 'ROOM01', a customer can hire this room, stating the period during which the room is to be hired. The 'orderitems' table has a field called duedate, which can represent the date the room is to be vacated, but a field has to be added to represent the starting date (it's definitely not equal to the date the order is opened!). Checking whether the room is vacant during this period is not as easy as it sounds. One approach would be to check whether there exists a record in the orderitems table for this room which straddles the starting date - if there is such a record, then the room cannot be hired. If the starting date is free, then the vacating date has to be checked in the same manner. Unfortunately this check would ignore the possibility that the room has been ordered for a smaller period, falling between the start and end date of this request. I'll leave this aside for the moment.

The room has its default price, so the total price for this line would be this default price times the number of days, which would be 1 + (the leaving date less the arrival date). The number of days would be stored in the quantity field, allowing the total price to be calculated automatically. This last statement implies that the quantity field should be read-only, which is not the case of the standard order items screen.

Priority does not allow fields to be set as read-only at run time, dependent on other fields. While the status of this field could be set in advance, it will be problematic if the user wants to use this screen to insert customer orders for items which are not rooms. As this standard capability should be left as it is, I am tending to suggest that a customised order items screen be used for room ordering, which implies that the order should have a specific type: an error message will be displayed if the user tries to enter the standard order items screen when the order type is 'Rooms', and vice versa.

The owners want to have a daily price, a weekly price and a monthly price. Priority allows multiple prices for the same part, based on a minimum quantity, so three rows can be entered for each part, with prices for quantity 1 (e.g. $50), 8 ($45) and 29 ($40). The appropriate price will appear automatically in the order line dependent on the number of days. Whilst this seems very good, there are two overwhelming problems with it, one internal and one external. The external problem can be phrased as 'what about weekends?' - how many days is it if one orders a room for two weeks? Is this from Monday 17 July 2017 until Friday 28 July 2017? What about the weekend 22-23 July? Is a week seven days or five days? Maybe some people work on Saturdays but not on Sundays. I call this an external problem because its solution is not dependent on Priority per se, but rather a management issue. Weekends can be handled by a series of flags, which will reduce the number of days, but this is messy.

The internal problem is that all the above relates to a room as if it is a whole, and not a room with workstations which can be hired separately. This relates to the quantity: how would a order show that the customer wants to rent 2 workstations for 5 days? The first solution appears to be separating the number of days from the quantity: if the room is defined as 'single hire' (and this has to be added somehow to the parts table), then the quantity will be 1, whereas if the room is defined as 'multiple hire', then the quantity will be however many workstations are required. The mechanism which calculates the total price will have to be amended (it's good that a customised screen will be used!) to calculate 'number of workstations' times 'number of days' times 'default price'. Unfortunately, this means that the multiple prices entered into the price list will not work, as the quantity is the number of workstations and not days.

I think that it would be better not to use the standard price list mechanism, but instead use a discounting system, which would state provide a discount per number of hiring days, for example 5% discount for a minimum hire of 8 days and 10% for a minimum hire of 29 days. This system can be implemented at three different levels: on a global basis (all rooms have the same discount policy); on a 'room type' basis (all rooms of a given type have the same policy), or each room has its own policy. The discount - by whatever means it is calculated - would be inserted into the discount field of the order line. So now the total price for a given room would be: 'number of workstations' times 'number of days' times 'room price' times (100 less discount) times 0.01. Management will have to decide at which level the discounting system will work - probably all three!

Although theoretically a check should be made whilst booking a workstation that there is a free workstation within the room during the given period, I have been told to ignore this possibility. Management is happy to rent 120 workstations a day in a room which holds only 100 workstations. Fortunately, in the first paragraph of this blog, I mentioned the need to add a field to the 'parts' table which allows differentiation between 'single' and 'multiple' hires, so this field would be checked prior to the complicated vacancy check, which is to follow.

Here is the vacancy check problem: someone wants to book ROOM01 for the period 17 - 28 July (this room is a 'single' hire). As it happens, someone has already booked this room for the period 19 - 25 July. How does the program 'see' that the room is unavailable and that the request has to be denied? A naive way of doing this is as follows: after a room has been successfully booked, a series of entries is made into a 'booked room' table, where each row has a room and a date. Thus given the above, this table would have the following entries

Room number	Date
Room01	19/07/17
Room01	20/07/17
Room01	21/07/17
Room01	22/07/17
Room01	23/07/17
Room01	24/07/17
Room01	25/07/17

The check would start from the day before the first required date (16 July) in a loop as follows (the flag has to be set initially to 'failure'):

1. increment date
2. if there is an entry for this date and room in the 'booked room' table, then go to label 99
3. loop until the current date is the same as the vacating date (28 July)
4. set flag to 'success'
5. label 99

Whilst this solution would work, it requires inserting values into a table, which is going to grow indefinitely. This table is not really needed, as the dates already exist in the order items table, which looks as follows

Room number	From date	Till date
Room01	19/07/17	25/07/17

The only difference between the code needed for these data as opposed to the earlier data is the second step, which would have to check whether the current date is in any given range. There is no particular advantage in this checking code, but the requirement to build the 'booked room' table has been obviated: a very big saving!