I have a webapp which displays user information about his house, rooms and objects in its house. There is a table events which consist all events which were associated with the house (rooms or its objects).
All these events created in another system and are comes to the app by integration.
The table events contains:
• event_guid (PK)
type contains object table name (flat, room, sensor etc.),
guid contains its guid in this table.
When the user open the page, he or she needs to see all the events happened in for the past 7 days.
fields type, guid and timestamp are indexed together.
Everything works fine when I just get an select by indexed fields from this table.
But now I have to add an additional information which is situated in another table (the needed row can be found by guid (events.gui) from table (events.type)). It's the object's name which stores in name field of one's table (tables flat, room, sensor etc).
I tried to add an additional table EventsView like:
But I think that it's stupid desicion.
Now I need for every founded event row to create another View object and add to it a name of object, like
Could you tell me is there any right proposal to solve this problem? And not to create 2*n objects for every ask about events.
Also I think that adding an additional field in events table and setting it during the integration is a bad desicion. Events objects create very frequently. And getting a value from a db before saving any events - it's slow.
Do I need to use a procedure in db? (I use mysql) But how can I create procedure where I try to join unknown table (depends on events.type field).