We will soon be rewriting an application that will allow the end user to define objects and fields on those objects.
I come from an MSSQL background and am familiar with options to handle this use case in an RDBMS: tacking on "CustomField1"-type fields onto tables; creating an Attribute table; using ALTER statements to alter the physical schema, etc. None of which sit well with me.
To complicate it further, data will artificially silo'd by a user's site. In other words, all the data will be in one database, but fields will be defined on a site level.
So I am starting to explore NoSQL (e.g. Mongo and Couch). I like the idea of storing a json document. It "feels" more natural for something like this. But this is new territory so a little scary. Really, this will be a "loose" schema. The objects themselves will be well known, but the fields could change. In RDBMS terms, we're talking 75+ table objects, including the main objects, lookup tables, many-to-many tables, etc.
My understanding is that NoSQL's primary benefits have more to do with scalability, which is not a concern here. This is an internal application and not a super-heavy load. No micro services or anything like that. So I haven't seen much in terms of when to use NoSQL when handling user-defined columns.
- Reporting. This is a sales app and getting aggregated data out is a must. We use SSRS and Tableau.
- We will be using a .Net Core api. Historically we use ORM's (NHibernate, Entity Framework). I'm not married to this approach but need to know the "equivalent" in a NoSQL environment.
- Performance. Our current stack--.Net 4.5, NHB, MSSQL--just isn't great for various reasons. How is NoSQL on pulling about large object graphs? How do you (or do you not) pull back "part" of a json document?
- Mass updates. Like in MSSQL I can do a
UPDATE mytable SET col1 = 'foo' WHERE col2 = 'bar'. Just want to make sure the same thing exists and it is performant in NoSQL!
Your thoughts appreciate on selecting the right database for user-defined objects and fields!