Ok that makes sense. I am a bit concerned that even though the update is a single statement, it must touch a lot of rows, but it should be infrequent enough to not be a problem.
My next question is regarding the very common operation of determining whether an output has been spent. Each output could be spent in a single transaction that belongs to multiple blocks (in separate forks) or by multiple transactions (also in separate forks).
So it seems that to determine if an output has been spent in a particular chain, you will have to query for all the inputs that spend the output, find (JOIN) the transactions that contain those inputs, find (JOIN) the blocks that contain those transactions, and see if any of those blocks have a span range that contains the chain in question. I know databases are supposed to be good at this kind of thing but it sure sounds like a lot of work. On the other hand, most of the transactions will not have a double-spend, so all the joining shouldn't make a difference in the most common case where there are no inputs that spend a txout. All the joining might slow it down a bit when the blockchain forks, but a few extra indexed lookups shouldn't be that big a deal I guess.
I'm thinking I'll switch to your schema with the spans, because it bugs me how my version has dynamic fields marking whether a block is part of the 'best chain' and whether a txout has been spent in the current best chain. It is a real headache to add a block to a fork other than the current best.
I plan to add a flag indicating whether an output is_final (and the parent transaction is_final - if all child outputs all have is_final set) and an output_address and for transaction a transaction_type (standard, generated and other) fields.
Also will be another table with chain_id, cumulative_difficulty... and cumulative difficulty in blocks.
For now still working on the block chain verification specifics (which algorithm is best and whether to organize then verify, pre-verify then organize then verify, or verify then link and organize). There are various pros and cons for all the different ways.