Thinking about Fauna's suitability for a project

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

Fauna is great for relational data, and the flexible Documents mean it’s possible to work with different data “types” in the same collection — or not even care about types and just have anything goes json data.

If the report data is always edited as a whole client side, that is always written and read as a whole, that sounds like it makes sense to store together. There is a transaction size limit of 16MB I think still in effect. frequently Reading 10000 separate documents would quickly become expensive. But making an edit to just a portion of huge generic json data might not be practical.

Built in ABAC rules make several schemes for multi tenancy possible. There are a few topics here discussing that.

Cloud/ API based makes setting up projects pretty nice! Authentication has its own challenges. There are now 3rd party auth providers available (for cost of higher tier account) but the account level covers ALL projects current and future. There are good security schemes without the cost of 3rd party providers though. I’ve found navigating this to be a bit difficult with no true canonical way to do things.

There’s a learning curve to FQL and ABAC rules etc. But it puts some powerful tools in your hands.