---
title: data model for CH2
tags: data_model
---

## Operations for CH1.5

// Insert transaction row
insert_to_tx_table(hash, trytes, milestone);

// Insert ADDRESS hint row (address hint with year/month clustered)
insert_to_hint_table(address, ADDRESS, year, month, milestone);

// Insert address data row (so we can query w/ (address, year, month))
insert_to_data_table(address,
                     year,
                     month,
                     address_kind, // (value < 0) ? INPUT:OUTPUT
                     timestamp_ms,
                     hash,
                     value,
                     milestone);
                     
let itr: [(&str, &str, &str, u8); 4] = [
          (trytes.trunk(), APPROVEE, TRUNK, 4),
          (trytes.branch(), APPROVEE, BRANCH, 6),
          (trytes.bundle(), BUNDLE, BUNDLE, 8),
          (tag, TAG, TAG, 10),
];
for (vertex, hint_kind, data_kind, query_id) in &itr {

    // Insert trunk/branch/bundle/tag hint row (vertex hint with year/month clustered)
    insert_to_hint_table(vertex, hint_kind, year, month, milestone);
    
    // Insert trunk/branch/bundle/tag data row (so we can query w/ (vertex, year, month))
    insert_to_data_table(vertex,
                         year,
                         month,
                         data_kind,
                         timestamp_ms,
                         hash,
                         value,
                         milestone);
}

## Tables for CH1.5

- Transaction table

CREATE TABLE IF NOT EXISTS keyspace_name.transaction (
    hash varchar,
    payload varchar,
    address varchar,
    value varchar,
    obsolete_tag varchar,
    timestamp varchar,
    current_index varchar,
    last_index varchar,
    bundle varchar,
    trunk varchar,
    branch varchar,
    tag varchar,
    attachment_timestamp varchar,
    attachment_timestamp_lower varchar,
    attachment_timestamp_upper varchar,
    nonce varchar,
    milestone bigint,
    PRIMARY KEY(hash, payload, address, value, obsolete_tag, timestamp, 
                current_index, last_index, bundle, trunk, branch, tag,
                attachment_timestamp,attachment_timestamp_lower,attachment_timestamp_upper, 
                nonce)
);

- Hint table

CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    year smallint,
    month tinyint,
    milestone bigint,
    PRIMARY KEY(vertex, kind, year, month)
) WITH CLUSTERING ORDER BY (kind DESC, year DESC, month DESC);

- Data table

CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    year smallint,
    month tinyint,
    kind varchar,
    timestamp bigint,
    tx varchar,
    value bigint,
    milestone bigint,
    PRIMARY KEY((vertex,year,month), kind, timestamp, tx, value)
) WITH CLUSTERING ORDER BY (kind DESC, timestamp DESC);

## CH2 Message Spec
- Specs
- [Transaction](https://github.com/iotaledger/protocol-rfcs/blob/faa8857719264f7b028bcf6712c021f5fb0fc6c7/text/0000-transaction-payload/0000-transaction-payload.md)
- [Milestone](https://github.com/iotaledger/protocol-rfcs/blob/002b8db2ab1ac044273e43dd1dce23745140436d/text/0019-milestone-payload/0019-milestone-payload.md)
- [Indexation]()
- [bee-message]()

## Supported API for CH2
- `messageID`
- which returns complete message payload
- Query Message Table
- `milestoneIndex`
- returns milestone index details
- Query Hint Table -> Data Table
- We calculate the milestone index details from the payload
- Another way: we store the milestone index details in Data Table
- `indexationKey`
- returns all items with matching indexation key
- Query Hint Table -> Data Table
- We return the messageID stored in the Data Table
- `addressHash` (hex or bech32)
- returns balance and outputIDs
- Query Hint Table -> Data Table
- We calculate the balance and outputIDs from the payload
- Another way: we store the balance and outputIDs in Data Table
- `outputID`
- returns output details
- Query Hint Table -> Data Table
- We calculate the output details from the payload
- Another way: we store the output details in Data Table
- `getMessageMetadata`
- Query Message Table
- We calculate the metadata from the payload
- Another way: we store the metadata in the Message Table
- `getMessageChildIDs`
- Query Message Table
- Another way: we store the ChildIDs in the Message Table

## Tables for CH2

- Message table

CREATE TABLE IF NOT EXISTS keyspace_name.message (
    id varchar, // messageID
    type int, // 0: Transaction, 1: MileStone, 2: Indexation
    payload blob, // The whole field for the given message type
    PRIMARY KEY(id, type, payload)
);

- Hint table
- Insert address/trunk/branch/bundle/tag hint row (vertex hint with shard clustered)
- kind
- MILESTONE (vertex is milestoneIndex)
- INDEXATION (vertex is indexationKey)
- ADDRESS (vertex is address)

CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    shard bigint,
    PRIMARY KEY(vertex, kind, shard)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);

- Data table
- Insert address/trunk/branch/bundle/tag data row (so we can query w/ (vertex, shard))
- kind
- MILESTONE (vertex is (milestoneIndex, shard))
- INDEXATION (vertex is (indexationKey, shard))
- INPUT (vertex is (address, shard))
- OUTPUT (vertex is (address, shard))

CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    shard bigint,
    kind varchar,
    id varchar, // messageID
    payload blob,
    PRIMARY KEY((vertex,shard), kind, id, payload)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);

## Data Model w/o Payload Calculation for Each API Calls

- Message table

CREATE TABLE IF NOT EXISTS keyspace_name.message (
    id varchar, // messageID
    type int, // 0: Transaction, 1: MileStone, 2: Indexation
    payload blob, // The whole field for the given message type
    metadata varchar,
    child_ids varchar,
    PRIMARY KEY(id, type, payload, metadata)
);

- Hint table
- Insert address/trunk/branch/bundle/tag hint row (vertex hint with shard clustered)
- kind
- MILESTONE (vertex is milestoneIndex)
- INDEXATION (vertex is indexationKey)
- ADDRESS (vertex is address)

CREATE TABLE IF NOT EXISTS keyspace_name.hint (
    vertex varchar,
    kind varchar,
    shard bigint,
    PRIMARY KEY(vertex, kind, shard)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);

- Data table
- Insert address/trunk/branch/bundle/tag data row (so we can query w/ (vertex, shard))
- kind
- MILESTONE (vertex is (milestoneIndex, shard))
- INDEXATION (vertex is (indexationKey, shard))
- INPUT (vertex is (address, shard))
- OUTPUT (vertex is (address, shard))

CREATE TABLE IF NOT EXISTS keyspace_name.data (
    vertex varchar,
    shard bigint,
    kind varchar,
    id varchar, // messageID
    payload blob,
    milestone_details varchar,
    balance bigint,
    outputIDs varchar,
    output_details varchar,
    PRIMARY KEY((vertex,shard), kind, id, payload, milestone_details, balance, 
                outputIDs, output_details)
) WITH CLUSTERING ORDER BY (kind DESC, shard DESC);

## Unsolved Questions
- The balance definition? (Not found in the transaction spec)
- The outputIDs definition? (Not found in the transaction spec)
- The output details definition? (Not found in the transaction spec)
- The metadata definition? (Not found in the transaction spec)
- ChildIDs definition? ((Not found in the transaction spec))