> If your data has a one-to-many relationship, then Document Database (NoSQL) is more appropriate.
Maybe. Probably not. But what happens when you want to query the inverse many-to-one relation? Unless your document store is effectively indexing these one-to-many relations as another document store with inverse relations (at which point you have an RDBMS regardless of what you call it), it’s probably going to be wildly inefficient.
Take the example of eCommerce. I tend to categorize eCom data into three units. Definitions, Transactions, State.
Definitions : Data that describe products, brands, categories, customers, vendors, etc. This has incredible variation and constantly evolving, with perhaps some core tenets being common across different business use cases.
Transactions : These are your sales, purchases, discounts, returns, etc. Not too much variation, more or less similar across various business verticals.
State : Inventory, stores, location of goods, etc. Least variance, can be referenced to Definitions and Transactions. Operations are standard and mostly fixed.
Choosing a PostgreSQL database, allows me to model Definitions as collections of JSONB items, with a few columns for id, time stamps, etc.
Transactions and state are best modeled as relational tables, with references to definitions.
How does the app code deal with the State/Transaction data? I assume the State/Transaction data will have columns that will comply with a Definition? How does the State/Transaction change after a Definition is changed?
Yup. Postgres feels very good with latest JSON type features. Even a really unpredictable JSON document has some metadata that you can stick in typed columns and get the full power of SQL over your little "NoSQL" column. Timestamps, author, etc.
Schema flexibility is another major differentiating factors between SQL and NoSQL. The relational world imposes a clear schematic structure during the design phase itself and any change in the structure is generally considered expensive. NoSQL offers Schema-on-Read. This makes it a lot easier to change the data format as data evolves.
I only worked briefly with a nosql 'real' production database but we needed to change the data shema, and migration was a pain (like any migration). Was it because some special user case ? Is migrating data really simplier with nosql for a reason I don't see ?
Naively I find them even worse because you can have multiple shema at once on the same table, so each read on an important document must include a sanity check 'what is the current shema of this particular entry, should we migrate it ?'. What do I miss ?
I've found Postgraphile to be an excellent solution for the "Impedance mismatch" for Postgres. All the strengths of SQL and in the app have access to a smooth graphql api without any effort.
Maybe. Probably not. But what happens when you want to query the inverse many-to-one relation? Unless your document store is effectively indexing these one-to-many relations as another document store with inverse relations (at which point you have an RDBMS regardless of what you call it), it’s probably going to be wildly inefficient.