I'm evaluating the migration of the following application's architecture: - Nginx + PHP + MySQL - Currently the infrastructure is scalable and redundant in the AWS cloud and It was designed to support one client.
Our next plan is to expand it to support multiple clients, these are my concerns: 1- The nature of the application is focused on collecting granular data by a set of companies with a task force of users based on a list of tasks per item in a given place. In summary: A user can potentially collect around 14.4k records per day (1 account x 2 campaigns x 12 actions x 12 tasks x 50 items) So a big client with 50 employees and 10 places to visit per day would record around 7.2M rows per day, anything can be measured ( a text, file as in a photo or video, etc) Our current way of storing the most granular values in a table is based on the EAV model. This allows the app to be very flexible, but as you can see generates a row vertically. making redundant much of the fk values and exponentially growing that table, giving us some issues when trying to run reports on this database, unless we offload the report somewhere else.
I was wondering if moving to: - Play + Mongodb And making the visit our main collection would reduce the redundant information. In the end a visit would be our most important document and would store 14.4k values, but everything would be contained inside. That means, in terms of documents, I would generate with 50 employes x 10 visit only 500 documents per day, which seems much more mangeable from the operations perspective.
However, the issue here is granular or cross-vertical reporting, like a report of your task force for the month. I guess this could be offloaded.
Would Play + Postgres be a better option and using the table with bjson data with slick?