Hi there,
I’m in the process of moving from a web app that I have loosely prototyped, towards a potential ‘proper’ architecture.
The way I have prototyped it works fine in terms demo-ing the Frontend etc, but I’m not sure it’s very good DB-ing going forward.
The problem that I can’t quite ‘square’ in my mind is that I think I need some hybrid of relational and document db types. As such, I’m wondering whether Fauna might be a good fit, and whether anyone might spare a few moments to point out the inevitable flaws in my thinking.
On the surface, this application would appear to be very ‘vanilla’ SQL territory; we have users, organisations, projects, reports … all the sorts of things that are easy to normalise into sql.
However, it’s these ‘reports’ that I don’t quite know what to do with. They are like mini-databases in themselves, and consist of metadata/overview, then nested zones, sections, sub-sections, and ultimately line-items. A report could easily contain 100,000 line items, and would rarely be less than 1000, with the average at probably 10,000.
Fo some additional arbitrary numbers, we might - say - have 1000 organisations who produce 100 projects, each having 10 reports.
One could easily normalise this out so that we go from ‘organisations’ all the way down to one gigantic table full of everybody’s ‘line-items’ … but this doesn’t sit right with me for some reason.
Firstly, the most common query will be to simply go and get a report [and ultimately throw it at the browser as json] … if a relational db has to assemble 10s of thousands of lines across the zones table, the sections table, the items table … I can’t help feeling this would be better if it was one big json store (or similar) … which would nicely encapsulate things.
Also, I have no real benefit from being able to easily query data from big tables containing all users’ data … e.g. there’s no real value in being to able to see how many ‘sections’ there are across all users, or what the ultimate average value of a line item is, or anything like that.
Furthermore, given the nature of the app, some sort of multi-tenancy would seem nice too, although I haven’t fully explored this.
The way I’ve prototyped it is to use mysql in what I would consider a ‘normal’ fashion for most of the app, but for these reports, I simply stuff the whole lot in a JSON field. I can’t help feeling this is a little foolish as well (but it works for the purposes of prototyping).
I would also very much like a cloud-based, fully automatic, hassle-free data store.
So, my apologies for all this waffle. I wondered whether anyone might point out (a) any errors in my thinking, but more importantly (b) whether Fauna might just offer the facilities I’m after. It might be a simple case of (for example) the max size of a document in Fauna is just too small, or something silly like that.
Thanks in advance for any thoughts