We all know that MYSQL is a relational database; And in the application that I am going to write, I need to use a relational database mainly because of sharing information between entities and joins on tables.
But I also have a very special condition, I need to keep track of some of events happening in the application. For example, lets say that I have a 'User' table with a field named 'XP', now I want to keep all the events resulted in change of 'XP' for this user.
Well, the most simple solution would be creating a new table and storing the events with a field pointing to the responsible user. But this tables gets overwhelmingly big and fills with unnecessarily rows, slowing the whole application.
If I was on a document oriented database, this wasn't a problem of course, but with MYSQL this tends to make everything slow.
I want to know your opinions here for possible ways to solve the problem and still keep the performance in a reasonable range. I didn't found any research or article related in the net.
PS, I had this idea about saving the changes as a JSON array in a field (of 'User' table), but then reading a field, converting it from text, adding an item and saving it again is very unpractical.
Thanks
What kinds of events are you keeping track of? Maybe it would be more practical to use a trigger to create events (so you don't have to do it manually on inserts/updates/etc). I'd also consider trying to separate the events based on the data you're dealing with into event-centric tables to reduce the amount of data you have to deal with on a given relationship (ex. a LOGIN_EVENT table that relates to a USER table on a USER_ID key, then a separate REPORT_REQUEST_EVENT table related to USER on the same key). By separating concerns you can create a more performant structure.