3 ms·

SQL focuses on the plural set of tuples. I want fields and indices. Joins and types on the fields are part and parcel of the engine. NoSql focuses on individia

SQL focuses on the plural set of tuples. I want fields and indices. Joins and types on the fields are part and parcel of the engine. 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.

pphysch 5y ago
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.
kumarvvr 5y ago
Absolutely. 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.
- raihansaputra 5y ago
  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?
  - kumarvvr 5y ago
    State is referenced to object id, state changes are stored as ledger entries, transactions are also ledger entries. All are referenced to object ids only.

pphysch 5y ago

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.

kumarvvr 5y ago

Absolutely. 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.

raihansaputra 5y ago
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?
- kumarvvr 5y ago
  State is referenced to object id, state changes are stored as ledger entries, transactions are also ledger entries. All are referenced to object ids only.