Would this data requirement suit a Document -Oriented database?

Question

I have a requirement to allow users to fill in journal/diary entries per day. I want to provide a handful of known journal templates with x columns to fill in. An example might be a thought diary; a user has to record a thought in one column, describe the situation, rate how they felt etc.

The other requirement is that a user should be able to create their own diary templates. They might have a need for a 10 column diary entry per day and might need to rate some aspect out of 50 instead of 10.

In an RDBMS, I can see this getting quite complicated. I could have individual tables for my known templates as the fields will be fixed. But for custom diary templates I imagine I would would need a table storing custom_field_types (the diary columns), a table storing entries referencing their field types (custom_entries) and then a third custom_diary table which would store rows matching custom_entries to diaries.

Leaving performance / scaling aside, would it be any simpler or make more sense to use a document oriented database like MongoDB to store this data?

This is for a web application which might later need an API for mobile devices.

Answer 1

I think the fact that you're asking about a document database is likely reason enough to use it. Generally the most important aspect to think about in choices like this is whether the team has skill/experience with a technology. If you're open to trying a document database, that might be enough.

But, from a more technological aspect, document databases generally mean things like no database-level joins, no transactions, no ACID guarantees. I say "generally" because each document database is different. Those sorts of things need to be implemented at the application level. If you don't generally have concurrent updates to data (or no updates, i.e. once a document is written, it's only read) then document databases are optimal because you may not need transactions.

If you're looking at structured but unspecific data then RDBMS, as you say, might be a lot of work. If each user has the potential to store what effectively ends up being custom-structured data, a document database seems like a better choice. But, it depends. A key/value store might also work very well in a situation like this.

If you're looking at things like ETL or reporting, then RDBMS' can sometimes be a better choice. They have a better selection of tooling to support things like this. Any replication needs might also influence whether you choose RDBMS or not. Some document databases support replication and many support sharding.

if you're looking at distribution of data, nosql in general is often a better choice. RDBMS can be made to work in situations like this but can end up with some performance issues. This usually isn't an issue until you reach very high loads over very big data.

Given the current state of document databases, I can't really think of many situations where they don't really fit from simply a data-storage point of view. There are very high-load big data situations where certain implementations might introduce some performance issues. But that's really rare--and often ends with the creation of a new nosql database designed specifically for that organization's scenario.

I would suggest implementing a quick and dirty proof of concept to get a feel for the work involved. If you think its drastically less work than an RDBMS then that might be the deciding factor--unless SQL-specific tooling like 3rd-party reporting (like BI) is a requirement.