Distributed Blockchain datastores provide inmutability to recorded transactions and only allow to add records accepted by a consensus algorithm in sequence. You could store data on the blockchain or have references to where data might live in other data stores. The blockchain does not seem to me like an ideal data structure to execute aggregate queries (assuming a client would allow for it) which a SQL-like database is good for. For example, if I want to know yearly income for a certain address in the ledger, one way to do it would be to create a flatten structure for all the elements in the transactions list field and execute a group by operation by address and year, and finally sum the amount field. A flattened structure would fit in a denormalized table. Also, there are ledgers that could record complex fields for each transaction, for instance, IoT device data. Is it absurd to think read-only SQL-like layers would be a good solution for making queries to extract data insight? What other benefits or disadvantages I am missing?
closed as unclear what you're asking by gnat, Martin Maat, Simon B, ChrisF♦ Mar 17 '18 at 14:51
Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.
Yes, and I believe you have already identified the benefits.
A blockchain structure is not a good structure for looking up data - you often have to search the entire chain. If you will be doing lookups, it is a very good idea to have some type of auxiliary indexing. One way to do so would be to copy all of the data from the blockchain into an SQL database.
There comes caveat in setting up an SQL-Like read-only DB for the blockchain ledger. To begin with, I am not a blockchain expert but I do have the basic knowledge to tell you in terms of data design how this would be a nightmare.
Firstly, an SQL-Like database requires a sorted data, for your distributed ledger fetching all of the blocks in a blockchain to set up a read-only DB would be the most expensive task.
Secondly, you won't be updating that readOnly DB per transaction, if you do that there would arise a problem of sorting the data. Thirdly, the information per account's identity is encrypted, usually in 34 characters (for BTC case) and having to sort things up or even a "group by" query to group the string oriented data would be a the addition to the evil witch hanging upside down from your ceiling fan.
You could get better results with NoSQL just because, even if the data is sort of organized, in the long run it is highly unorganized, and thus if you could just save the data based upon blockchain-user-account-id and add in values accordingly or perhaps a 'history' object, that could turn out to be a better option for you.
So far I can see the complexity of such solution through your spectacles as n^2. Perhaps NoSQL could squeeze things for you in this scenario.
The rest is all your choice. Have a nice day. :)