If you're only interested in storing the best chain then a fairly simple schema is possible.

CREATE TABLE blocks (
    hash bytea NOT NULL PRIMARY KEY,
    index integer NOT NULL UNIQUE,
    CONSTRAINT block_hash_size_check CHECK ((octet_length(hash) = (256 / 8)))
);

CREATE TABLE transaction_inputs (
    output_transaction_id bytea NOT NULL,
    output_index integer NOT NULL,
    block_index integer NOT NULL,
    CONSTRAINT transaction_id_size_check CHECK ((octet_length(output_transaction_id) = (256 / 8))),
    PRIMARY KEY (output_transaction_id, output_index)
);

CREATE INDEX transaction_inputs_block_index_idx ON transaction_inputs USING btree (block_index)

CREATE TABLE transaction_outputs (
    transaction_id bytea NOT NULL,
    index integer NOT NULL,
    amount numeric(16,8) NOT NULL,
    type character varying NOT NULL,
    addresses character varying[],
    block_index integer NOT NULL,
    spent boolean DEFAULT false NOT NULL,
    CONSTRAINT transaction_id_size_check CHECK ((octet_length(transaction_id) = (256 / 8))),
    PRIMARY KEY (transaction_id, index)
);

CREATE INDEX transaction_outputs_addresses_idx ON transaction_outputs USING gin (addresses);
CREATE INDEX transaction_outputs_block_index_idx ON transaction_outputs USING btree (block_index);

On 06/05/2013 05:53 PM, Marko Otbalkana wrote: