I'm having a system that require a “routing configuration” for events. I need some efficient database structure and query's. I have only limited SQL knowledge, so I hoped someone can tell me a bit more to getting started.
The inputs are organised in three 4 levels (Buildings, Rooms, Units, Interface). Every interface is capable to generate say 32 different events. Beside of that there are also a couple of periods.
The configuration is done using a user interface organised as wizard, first the user select one or more periods he want to change. Then a tree view with selection boxes will be displayed that enable hem to select the event sources (so a complete building, a room, a unit or interface of an combination of that). After that selection he select the events he want to route to a different event handler. And at last the event destination.
When an event is generated the database must be asked witch event handler must handle the event.
My first thought was to make a table that contain for each period and for each interface event an entry. But I think this is not very efficient. It result in an enormous table that would be very difficult and slow to update. (Example for a typical quantity: 10 Periods x 4 Buildings x 20 Rooms x 32 Units x 4 Interfaces x 32 inputs = ~3M rows., so an update for a single period, one event for a complete building requires ~300k row updates.)
The idea is that someone can send all events of a specific kind for a complete Building to a specific event handler. But still have an opportunity to make an exception for a specific case.
If you have an idea in which direction I should think then let me know.