2

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.

Concerns:

  • 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!

Thanks Tom

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.