Data Models for SQL and NoSQL: Horseshoe Pattern
myunderstanding.substack.com
myunderstanding.substack.com
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.
I'm not sold on the argument myself, but I did not keep track recently.
I guess the logical convergence point is not an sql or nosql db, but a datastore, that allows for mixed use.
NoSql focuses on individial key/value pairs. Indices and types and joins have to be stapled onto the opaque value.
SQL is more mature and general. NoSql has its place. Eningeering is grasping the virtues of both and implementing what is appropriate.
Convergence is going to land on the SQL side.
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.
All are referenced to object ids only.
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 ?