I was wondering if I could ask for some quick opinions.
I am prototyping an app which seems simple but I can’t readily arrive at an architecture that feels right.
We have data that is simple and fits well into standard sql format, eg users, organisations, projects, reports.
However, the ‘reports’ are the problem. They can typically be between 1000 and 100,000 'line items'. Each item with around 10 fields. A bit like a large spreadsheet, and in fact this is where they originate usually.
A user (lets say 100 of them) generates say 10 projects a year, and each project has say 10 reports, and each report has say 10000 items (or rows) .. then if there was one massive table quickly growing to millions of rows, with each row ‘belonging’ to a report etc .. well this doesn’t feel right.
To make this more tricky, the reports themselves can almost be considered 'mini-databases' in themselves, in that although the meat of the data is thousands of 'line items' (with uniform column requirements), they are structured into 'zones', 'sections', 'subsections', and there is also an 'overview/meta' section as well.
In theory, I could normalize this right from 'user' to 'item' something like users->projects->reports->zones->sections->subsections->items ... but again this just doesn't feel right to me; what the DB has to do to simply go and get a report seems overkill. Also all of this report info would then be all mashed together for all the different user accounts.
Every report query would have to go a collect thousands of rows out of millions, and assemble the client json from different tables, and there would be no other real benefit from being able to query the table in this way. All the reports are self-contained really.
As such, I am wondering whether this a case for a nosql route, like mongo or documentDB, where I can just throw thousands of lines of json in a Report Collection or similar. This would have further implication though, as the backend frameworks I like to use don’t play all that nice with nosql, and we lose a bit of the standard easy relational schema that would still seem to fit the other tables of the app (e.g. user accounts, RBAC, users belonging to organisations, etc).
.. but there would be a similar question with a json store .. is it better to maintain a 'tree' structure, and keep all the line items within their sections, and sections within their zones etc, or normalize out so there is one big load of lineitems, that have references to their sections within them, etc.
I’ve messed around with using MySQL for everything, and using the json field type to store the reports as one big json, but I don’t know whether this is pushing the intention of the field-type a little, and it’s hard to query into.
A consideration here is that on the frontend, users will update one (or a few) row(s) at a time, and I need to be able to handle that. In this way, I suppose there's a similarity with google sheets, or airTables or something. Do these big apps tend to use rdbms?
I notice there seem to be more and more database options appearing all the time now, eg fauna supabase etc .. makes it even more tricky to choose the right solution.
Thanks in advance for any tips