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?

  • the update statement? – Ewan Mar 4 '18 at 18:03
  • @Ewan if I understand your statement let me add that the Relational layer should not allow for updating of previous records. It should strictly allow read type queries. – KJP Mar 4 '18 at 18:05
  • 1
    I do not think there is a point considering the entire block chain is basically one table. So there is nothing relational about it. A stream seems a more appropriate model. – Martin Maat Mar 4 '18 at 18:48
  • @MartinMaat if it is a big table then would you agree there is a benefit to adding a SQL like query language to obtain insights? Also, do you have a reference to what you mean by the stream model? I update my question to have more context of what I had in mind. – KJP Mar 4 '18 at 20:32
  • @KJP I cannot say, do not know what insights are in this context, not after reading your update either. The whole question is starting to look pointless to me. Whenever I read up on block chain to learn what the fuss is about I end up thinking to myself it is all a useless unscalable load of bull that has even more political issues than technical ones. So until I see the light I think it is best for me to retreat from this discussion. – Martin Maat Mar 5 '18 at 7:50

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.

| improve this answer | |

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. :)

| improve this answer | |

Not the answer you're looking for? Browse other questions tagged or ask your own question.