Some thoughts....
Typically one does not want to store pieces of tightly interrelated information in different systems. The chances of things getting out of sync is significant and now instead of one problem on your hands you have two. One thing you can do with Mongo though is use it to pipeline your data in or data out. My preference is to keep everything in PostgreSQL to the extent this is possible. However, I would note that doing so really requires expert knowledge of PostgreSQL programming and is not for shops unwilling to dedicate to using advanced features. I see a somewhat different set of options than you do. Since my preference is not something I see listed I will give it to you.
You can probably separate your metadata into common data, data required for classes, and document data. In this regard you would have a general catalog table with the basic common information plus one table per class. In this table you would have an hstore, json, or xml field which would store the rest of the data along with columns where you are storing data that must be constrained significantly. This would reduce what you need to put in these tables per class, but would allow you to leverage constraints however you like. The three options have different issues and are worth considering separately:
hstore is relatively limited but also used by a lot of people. It isn't extremely new but it only is a key/value store, and is incapable of nested data structures, unlike json and xml.
json is quite new and doesn't really do a lot right now. This doesn't mean you can't do a lot with it, but you aren't going to do a lot out of the box. If you do you can expect to do a significant amount of programming, probably in plv8js or, if you want to stick with older environments, plperlu or plpython. json
is better supported in 9.3 though at least in current development snapshots, so when that version is released things will get better.
xml is the best supported of the three, with the most features, and the longest support history. Then again, it is XML.....
However if you do decide to go with Mongo and PostgreSQL together, note that PostgreSQL supports 2 phase commit meaning you can run the write operations, then issue PREPARE TRANSACTION
and if this succeeds do your atomic writes in Mongo. If that succeeds you can then COMMIT
in PostgreSQL.
This seems a little outside the scope of a StackExchange question. However.....
NoSQL databases are, typically, build to resolve specific issues with the relational model. The most common issue addressed is scalability. However, because they're all designed to address different aspects of certain problems that some applications have with the relational model, there really isn't something you can say about all of them as a whole. Maybe you need to handle terabytes of data? Or maybe you need to handle a data schema that is very dynamic across the board and an EAV type schema would just kill performance? Maybe you want a data store that cares about availability more than consistency (see CAP theorem)? Each one is good at something that could be wildly different from what another is good at, while an RDBMS is a much more generalized database.
The answer I hear from people I trust on this topic is that if you don't already know why you need to abandon a traditional RDBMS for NoSQL, then you almost certainly should stick to an RDBMS. There's a reason it took 30 years before people began to seriously consider data store models beyond the RDBMS. If your queries are requiring features not available in MySQL/MariaDB, you may wish to consider another RDBMS that has a more robust feature set. That could be PostgreSQL, or Oracle, or MS SQL Server.
Your proposed schema here already suits the relational model fairly well. You may need a few junction tables for many-to-many relationships, but I don't think you necessarily will see many of those. If you properly size the DB, properly index your tables, and analyze your common queries -- things you have to do with NoSQL, too -- I don't see that you'd have a huge problem. You may even be able to get away with views for the most common queries.
Best Answer
In general, clustered-NoSQL databases offer you better horizontal scalability. So, as you scale you can get higher capacity (both memory & compute power) by simply adding new nodes. So, it will be a good insurance for the future.
When it comes to ACID properties, NoSQL databases offer flexibility. So, it boils down to how much ACIDness you want and how much performance you are willing to give up for it. Seems that you do not need very high level of ACID. I am sure you can get good performance in lieu of that.
PS: I do not want to comment specifically about MongoDB. You should do your research and choose the NoSQL db that fits your performance & functional needs.