Seems to be a lot of discussion about moving data services out of oracle for OO data like JSON into NOSQL databases ......... Im kind of curious to look at the capabilities of JSON localized within the DB.
One of the main "gripes" I have with JSON data/NOSQL is that if its not self contained (data has to end up in a relation system for other purposes) then why not store it in the DB and leverage capabilities of both being able to be queried directly and maintaining 1 system rather than 2 and have the benefits of joining directly to JSON data nad vice versa if required.
So ..... going to do a few POC.
Have a main question for today ..... around data change capture.
If I [for lack of a better term] aggregate my relational data into Object notation like JSON ...... and store than DATA in JSON format within the DB ............ and then use JSON as the caching/DOC/Schemaless interface to my app ........... if I change the the JSON and/or the base data within the relational tables ...... whats the best way to keep that in sync ?
Thinking basically triggers ...... or some posted event into a custom table ......... but anyone looked into this ?
Example might be.
I have all customer data in customer table and all order data in orders table and I join those to get all the relevant customer and order information since company was created - say 20 years of orders. That query can be somewhat expensive depending on data model.
1) I create a JSON summary document of the customer and all their orders and store it in JSON.
2) Application just comes and gets customerID= 1234. Gets the current customer info and all the data - no large SQL or join. And fast.
But ...... Customer changes Surname due to marriage. Maybe the applicaiton updates the JSON document direct. How to propagate that directly to the relation CUSTOMERS table automatically.
or more likely
Another backend process makes a change to the orders table to say - hey the product shipped. How to update the JSON data for "Customer" experience data.
Obviously it can be done programatically - i.e if you update the order go and also update the customer data JSON - but if it can be handled through CDC - then cuts down the effort significantly.