How to store events and rules

[joakimbengtson]

Go to https://cacoo.com/diagrams/1YMHGARTs2lx4636 and take a serious look at the tab 'Set rental slot and price'.

As you can see the user will add "rules" about when a rental object can be reserved. When a rental object is an event (wine tasting, rock climbing, c++ course, ...) the user adds an actual date/time for the event.

But it would be nice if a reservation can be made with the same logic regardless of the rental object is a "thing", resource or event.

How to store rules/dates in the database? How can fast reservations with complex rules/dates be done? All ideas/thoughts/input are welcome!

(This is pasted from mail correspondence, but I think its good that all can see this)

[joakimbengtson]

Regarding your question, I'd like to know first which kind of rental object will we have in our system. It's pretty clear to me that we'll have "events" but I don't know what "resource" mean to you for example.

Also, do you refer to a technical point of view when you speak about the "same logic"? At a first glance, to me these rules should be a "Model" in our server project, but I need to know the answer of the above questions before I can give further details.

Regards, Jose

[joakimbengtson]

Hi, The final goal is to allow reservations of 3 categories: hardware/things, resources and events.

Hardware - Skies, tents, cars, rooms, bikes, tennis court etc etc Resources - A human, masseur, handyman, doctor, hairdresser, stand up comedian, lecturer, lawyer, artist Events - WIne tasting, rock climbing, jQeury course…

Reservation of hardware and resources are quite similar. They are available certain times (i.e. 08.00 - 17.00, mon - fri) over an extended period of time, typically year around. Here the only thing stored is the "rules" that describes when they are available (and to what price).

Events are only available at specific dates/times (i.e. 2014-05-12 18.00) and can't be reserved on other dates. As I see, these dates have to be created and stored in the database.

So yes, "same logic" is from a technical point of view. Should there be two tables in the database, one with rules and one with dates? What is the smartest approach to make reservations as easy and fast as possible (from a programming/logic point of view).

/Joakim

[joakimbengtson]

As I see it, we should try to unify the logic as much as we can. This, related to that specific case, would mean to integrate the dates inside the Rule model. I'm speaking of Ruby on Rails models, not directly about tables in the database as we rarely will work with that directly. Having said that, I wonder why we think there're so much difference in the three cases, to me it's more or less the same. For example, even if the events are only available once or twice per year, in contrast with resources or hardware, the basic structure should remain the same, it's basically a point in the calendar instead than two, three, four... In Google Calendar they allow you to "tick" some fields like "every day", "only once", "each week", "each month"... I wonder if we can do someting similar in the frontend... and use these logic sentences as "rules" in the backend. This way we have both a common structure in the backend that support a flexible structure (just by defining normal rules) regardless of the categories we define later on, and a straightforward interface that users can understand really fast.

Is it more or less clear what I mean?

Regards, Jose

[joakimbengtson]

Hello Jose,

I agree, there should not be a technical difference between the three cases. If you book something, you book it! But there are some differences regarding availability.

"Hardware" may be booked at any time depending on the object. Some may only be available at specific hours, others (like cars) you pick up and leave when you want to. "Resources" have their "opening hours" when they may be booked. A doctor probably wants to work from 08:00 - 17:00 (or whatever) and the patient can book any available time during that period. "Events" have special dates when they may be booked.

The common thing is to be able to store when the object MAY BE booked and when it REALLY IS booked. Right now only the latter is working.

For events, it is simple. This can be implemented with just a set of start dates and end dates. I don't think there is another way to store it.

But resources and hardware typically have "weekdays", "08:00-17:00", "not on holidays" and maybe "closed for lunch". So if there would be no technical differences between the three cases, we must store this as a series of dates. But this means "generating" a large amount of dates acting as start dates and end dates.

Another thing, each rental object may have different pricing depending on time of day (think tennis) or day of the week (think hotel rooms). So there is another set of start dates and end dates we need to store somehow.

Pros: Simple approach. It is easy searching the database. Cons: Requires some kind of generation of dates from rules. To change a rule requires some kind of regeneration.

As you mentioned, the other option is to store rules somehow. Right now I don't have a good solution for this. I am still thinking about it.

Pros: No need for generation. Cons: How do you store the rule "The tennis court is only available mon-fri between 08:00 and 20:00 and can only be rented by the hour". How do you search a thing like this?

Just some of my thoughts right now... ;)

Regards, Magnus Egelberg

[mounte]

So, first two wild shots... any comments on using a rules-engine server side if we have more complex scenarios... see for instance: https://github.com/ulfurinn/wongi-engine https://rubygems.org/gems/condi --> http://rubydoc.info/gems/condi/1.0.0/frames

Next, to get started what about implementing for resources and items and then find out what the missmatch would be for events or if the imposed conditions for events would be solved "automatically" by the first implementation.

[meg768]

I was more thinking of how to store single and recurring events in the database and how to search these. Right now I am looking at the iCalendar specification at http://tools.ietf.org/html/rfc2445#section-4.8.5.4

In that specification the following statement...

Every other week on Monday, Wednesday and Friday
until December 24,1997, but starting on Tuesday,
September 2, 1997

...is represented like this.

DTSTART;TZID=US-Eastern:19970902T090000
RRULE:FREQ=WEEKLY;INTERVAL=2;UNTIL=19971224T000000Z;WKST=SU;BYDAY=MO,WE,FR

This could be a good way to go since it is well documented. How this is stored in the database is another matter. I found this, http://stackoverflow.com/questions/1054201/ical-field-list-for-database-schema-based-on-ical-standard. A Rails version would be nice! ;)

Someone must have done this before! I'll google some more...

[meg768]

Found this, https://www.ruby-toolbox.com/categories/recurring_events.

It seems like "Ice Cube" supports serialization which means that you can store it as a binary, YAML text or iCalendar text. It even supports exception times.

Also found this, https://github.com/jkbr/rrule, if needed on the client side...

[javidgon]

@meg768 I´ve made an schema with a unified proposal for implementing the different rules/exceptions for the different types of events. I´ll try to post it here during the next days in order to get some feedback.

[javidgon]

This is the schema. I hope it's understandable. It follows the idea of rules that I mentioned in my other post. Don't pay too much attention to the details cause I made them fast (attribute types or names), just to the main idea.

[meg768]

Yes, I see, but I am not sure we are talking about the same thing. Or maybe we are? ;)

What I need is how to store information so that the user may define rental availability. Take a look at http://jkbr.github.io/rrule and paste the following text under the tab "RRULE string".

FREQ=MINUTELY;DTSTART=20140101T090000Z;INTERVAL=45;WKST=MO;BYDAY=MO,TU,TH;BYHOUR=10,11,12

The date/time table on the right would give you available squash courts on Monday, Tuesday and Thursday from 10:00 to 12:15. All bookings must be made on these times. You cannot book on weekends, Wednesdays and Fridays.

So I was thinking of maybe creating a table "Slots" or "Availability" containing the following fields.

id:integer rental_id:integer rule:text price:decimal duration:integer

So every rental object has a set of "slots" defining the price for different times of the day. We could actually add another row defining the price and availability of weekends.

This approach assumes we store the iCalendar format in the "rule" column. This is fine, since we may use "Ice Cube" on the server and "rrole.js" on the client to get a list of "slots" which are available. But this also rules out making SQL-queries directly. You can't do something like

SELECT date WHERE date MATCHES "FREQ=MINUTELY;DTSTART=20140101T090000Z;INTERVAL=45;WKST=MO;BYDAY=MO,TU,TH;BYHOUR=10,11,12"

But I don't know if this is a problem or not...

[joakimbengtson]

https://github.com/bookio/client/blob/master/docs/Samples%20of%20rules.md

I have tried to put together some samples of different rental objects. My hope is that this makes it more clear what we need to store in the database, and how different rental objects has to be described.

The "strange" sample of Segway rentals is fetched from a swedish site, the reason for the many time intervals is that its cheaper to rent for longer time. A sort of tiered volume discount (if you assume the unit is rented in counts of 30 minute periods)?

Another hard nut to crack is the hairdresser. Here an interval could differ in price depending on the treatment given (this is the same for many objects in the resource-category), ie not a fixed price for 45 minutes. Different treatments can also be different in duration.

[javidgon]

@meg768 Actually I think we were talking about the same, but I was proposing a ¨only backend¨ solution instead than a ¨client-backend¨ solution. There´re many ways to solve this issue I guess, I was just proposing a possible way. I see your point about using ´rrule´ as it probably makes us to save a lot of time, but as you said, it might require an extra tweaking...

Anyway, I´m aware it´s a tricky subject, I´ll play around with the ´rrule´ library you proposed to see if something comes to my mind.