Hacker News new | ask | show | jobs
by rmrfrmrf 3217 days ago
The example you've used (invoices) is actually quite instructive for demonstrating the benefits of a "document store." An invoice, historically, was a literal printed piece of paper. Invoices are actually really annoying to implement in an RDBMS because of so-called "referential integrity" -- an invoice should be a "snapshot in time" of everything that happened when the order was processed, so ideally, when a user views their invoices from the past 2 years, they look the same every time.

Except, oops, your user got married and moved, now your precious "referential integrity" means jack because the generated invoice is flat-out wrong. Product removed from the store? Too bad, needs to stay in the database forever for historical purposes. Prices need to change? Better design the database to handle snapshots of every product state.

If you were implementing this in MongoDB, you'd probably store a UUID and the flattened data at the time of invoice generation, that way you can still query on ids AND not deal with the headache of having a combinatorial explosion of data in your RDBMS.
3 comments
icedchai 3217 days ago
You would solve this in a RDBMS the same way: de-normalize when you're saving the invoice (example: a line items table with snap shot of current item price, description, etc.)
link
rmrfrmrf 3217 days ago
Yes, which suggests that the "serious problems" mentioned by the grandparent aren't serious (or problems) at all.
link
purerandomness 3215 days ago
In Postgres, you'd simply have a table with a JSON column for the snapshot-in-type contract.

You can then select fields from that JSON for invoices, reports, etc with the arrow operator:

https://www.postgresql.org/docs/9.6/static/functions-json.ht...

link
wvenable 3217 days ago
With SQL you can denormalize all that (and should) to create that snapshot. But with NoSQL you can't normalize and get back a way to quickly query the number of products sold per month over the last 5 years.
link
chrisco255 3217 days ago
Yes, this is possible with Aggregation and MapReduce: https://docs.mongodb.com/manual/aggregation/
link
wvenable 3217 days ago
For relative values of "quickly".
link