Oct 29, 2024

So now you need navigation from header->txs->tx->inputs->input->output->tx->header->confirmation_state. Of course you already needed navigation from height->header->txs->tx->inputs and ->tx->outputs even if just so that a block can be reconstructed. But you also need to parse out scripts and witnesses as otherwise store queries, such as this confirmability verification, would be horrendous as they don't require these massive amounts of data (scripts/witnesses are most of the chain). So tack on input->script/witness, and output->script as additional factoring (probably have to be independent tables). Somewhere back there (I was in Milan when it came to me) you realized that there needs to be an association table between header and tx, because headers get stored before blocks (transactions) arrive (or you could just keep all headers in memory until blocks arrive :{, and you have no way to know how many txs will arrive. Which is why there is a txs association table. If only you had a random access store you could write the association into the header table, and not have to implement an index for txs, based on the header surrogate key. But alas, append only makes the store fast, so tough call.