Master and slave event tables

Maptiming will be a service based on Google Maps and Google Fusion Tables to store and publish event data. The app wil visualize the events on a map and provide¬†filters to find events within a certain timeframe. A user can also grab a link to sync his favourite view on the map to his personal calendar. Here’s a word on the underlying data structure.

The event data is stored in two tables, a ‘master’ table and a ‘slave’ table. The main reason for this setup is to manage recurring events.

The master table contains only one row for a recurring event. One of its fields is the recurring date rule, according to the RFC 2445 iCalendar specification. This table will be queried when the event data is accessed in iCal format, e.g. when a user has linked a maptiming view in his personal calendar.

The master table cannot be used to query for events on a specific date, because it only stores the recurring date rule and no individual dates. The master table is therefor synced into a slave table. It stores roughly the same data as the master table, but it has one row for each instance of a recurring event. This table is the back-end for the Maptiming web app.

How many events can be stored?

Managing table size will be a concern. I haven’t done any performance tests yet, but there’s a hard limit on the number of rows allowed in a Fusion table, of about 100.000. Having 100.000 events in the master table is not very realistic, considering that old events can be wasted automatically, but by expanding recurring events in the slave table, the limit may be within reach for large organizations with events recurring on daily basis, e.g. a store with 100 shops, open 7 days a week with opening hours stored as recurring events, will generate 7 x 365 x 100 or more than 250.000 entries if the events are expanded for up till one year. An automitic limit on the expansion timeframe will be required.

A row in the master table that defines a recurring event, expands to multiple rows in the slave table.
A row in the master table that defines a recurring event, expands to multiple rows in the slave table.



Leave a Reply

Your email address will not be published. Required fields are marked *