TallyBox Tutorial - Database Structure

TallyBox Database Structure

This tutorial guides developers through the SQL Server database structure for the TallyBox Windows desktop application. The `net_mixoftix_tallybox` database supports transaction processing, wallet management, and archiving for a decentralized ledger system. This tutorial explains the database setup, table purposes, relationships, and provides a downloadable SQL script with copy-to-clipboard functionality.

Step 1: Database Setup

The `net_mixoftix_tallybox` database is created with specific configurations for performance and reliability. The setup involves:

• Create the database with a primary data file (10MB initial size, unlimited growth) and a log file (8MB initial size, 2TB max).
• Set compatibility level to 160 (SQL Server 2022) for modern features.
• Enable Query Store for query performance tracking and set recovery model to FULL.
• Configure settings like `AUTO_UPDATE_STATISTICS`, `PAGE_VERIFY CHECKSUM`, and disable features like `ANSI_NULLS` and `ANSI_PADDING`.

CREATE DATABASE net_mixoftix_tallybox
    PRIMARY_FILE = 'C:\shahin_root\dbs\net_mixoftix_tallybox.mdf', SIZE = 10304KB, MAXSIZE = UNLIMITED
    LOG_FILE = 'C:\shahin_root\dbs\net_mixoftix_tallybox_log.ldf', SIZE = 8192KB, MAXSIZE = 2048GB
    COMPATIBILITY_LEVEL = 160
    QUERY_STORE = ON (STALE_QUERY_THRESHOLD_DAYS = 30, MAX_STORAGE_SIZE_MB = 1000)
    RECOVERY = FULL
    PAGE_VERIFY = CHECKSUM
    DISABLE ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS
END
                

References:
CREATE DATABASE (Microsoft Docs)
SQL Server (Wikipedia)

Step 2: Download SQL Script

Below is the complete SQL script for creating the `net_mixoftix_tallybox` database and its tables. You can copy it to your clipboard.

References:
Generate Scripts in SSMS (Microsoft Docs)
SQL (Wikipedia)

Step 3: Transaction Processing Tables

These tables handle incoming transactions and their processing. They include:

• tbl_order_dispatcher_0 to tbl_order_dispatcher_9: Sharded tables for incoming transactions, each with fields like `graph_from`, `wallet_from`, `order_amount`, and `the_sign` (primary key).
• tbl_order_ods: Holds transactions moved from dispatcher tables for processing, with the same structure as dispatcher tables.
• tbl_order_ods_multiple: Stores multiple recipients for group transactions, linked by `the_sign_md5`.

CREATE TABLE tbl_order_ods (
    graph_from VARCHAR(50) NULL,
    graph_to VARCHAR(50) NULL,
    wallet_from VARCHAR(64) NOT NULL,
    wallet_to VARCHAR(64) NOT NULL,
    order_currency VARCHAR(10) NULL,
    order_amount DECIMAL(18,8) NOT NULL,
    order_utc_unix BIGINT NOT NULL,
    order_id VARCHAR(20) NULL,
    public_key VARCHAR(50) NULL,
    the_sign VARCHAR(100) NOT NULL,
    local_utc_unix BIGINT NULL,
    CONSTRAINT PK_tbl_order_ods PRIMARY KEY (the_sign)
)
                

References:
CREATE TABLE (Microsoft Docs)
Database Sharding (Wikipedia)

Step 4: System Configuration Tables

These tables store configuration and validation data for the TallyBox system. They include:

• tbl_system_config: Key-value pairs for system settings (e.g., `config_key`, `config_value`).
• tbl_system_currency: Defines currencies (e.g., IRR, 2ZR) with `currency_id` and `currency_name` (primary key).
• tbl_system_graph: Maps graph domains to IDs (e.g., `graph_domain` as primary key).
• tbl_system_peers: Tracks peer nodes with `ip` (primary key), `port`, and `status`.
• tbl_system_treasury: Manages treasury transactions with `treasury_id` (primary key).
• tbl_system_gazette: Logs blockchain-related data (no primary key).

CREATE TABLE tbl_system_currency (
    currency_id INT NULL,
    currency_name VARCHAR(10) NOT NULL,
    currency_title VARCHAR(50) NULL,
    currency_image VARCHAR(15) NULL,
    CONSTRAINT PK_tbl_system_currency PRIMARY KEY (currency_name)
)
                

References:
Primary Key Constraints (Microsoft Docs)
Database Normalization (Wikipedia)

Step 5: Ledger and Wallet Tables

These tables manage wallets and ledger entries for transactions in the TallyBox system. They are critical for tracking wallet identities, public keys, and transaction records. The tables include:

• tbl_tallybox_wallet: Maps wallet addresses (`the_wallet`, primary key) to unique `wallet_id` values for efficient referencing.
• tbl_tallybox_wallet_pubkey: Associates public keys (`public_key`) with `wallet_id` (primary key) for cryptographic validation.
• tbl_tallybox_book: Records ledger entries for transactions, including `tnx_id`, `currency_amount`, `left_amount`, and `tally_hash` (primary key).
• tbl_tallybox_sign: Stores transaction signatures and metadata, with `tnx_id` (primary key), `the_sign`, and `the_sign_md5` for verification.

CREATE TABLE tbl_tallybox_book (
    tnx_id_dag DECIMAL(18,7) NULL,
    tnx_id DECIMAL(18,7) NULL,
    tnx_type TINYINT NULL,
    graph_id INT NULL,
    wallet_id BIGINT NULL,
    currency_id INT NULL,
    currency_amount DECIMAL(18,8) NULL,
    left_amount DECIMAL(18,8) NULL,
    tally_hash_dag VARCHAR(64) NULL,
    tally_hash VARCHAR(64) NOT NULL,
    CONSTRAINT PK_tbl_tallybox_book PRIMARY KEY (tally_hash)
)
                

References:
Primary Key Constraints (Microsoft Docs)
Cryptographic Hash Functions (Wikipedia)

Step 6: Buffer and Archive Tables

Buffer and archive tables manage temporary storage and long-term archiving of transaction and wallet data. These tables include:

• tbl_tallybox_wallet_buffer, tbl_tallybox_wallet_pubkey_buffer, tbl_tallybox_book_buffer, tbl_tallybox_sign_buffer: Temporary storage for records before archiving, with an `archive_id` (1 or 2) to determine the target archive table.
• tbl_tallybox_wallet_archive_1, tbl_tallybox_wallet_archive_2, tbl_tallybox_wallet_pubkey_archive_1, tbl_tallybox_wallet_pubkey_archive_2, tbl_tallybox_book_archive_1, tbl_tallybox_book_archive_2, tbl_tallybox_sign_archive_1, tbl_tallybox_sign_archive_2: Archive tables for long-term storage, sharded into two tables for scalability.

CREATE TABLE tbl_tallybox_wallet_buffer (
    the_wallet VARCHAR(64) NOT NULL,
    wallet_id BIGINT NOT NULL,
    archive_id TINYINT NULL
)
                

References:
Temporal Tables (Microsoft Docs)
Data Archiving (Wikipedia)

Step 7: Optimization and Monitoring

The database includes features for optimization and monitoring:

• tbl_optimize_row_counts: Tracks row counts for each table (`table_name`, primary key) with `row_count` and `last_updated` for performance monitoring.
• Indexes: Non-clustered indexes on `tbl_tallybox_book`, `tbl_tallybox_sign`, `tbl_tallybox_wallet`, and archive tables optimize queries on `currency_id`, `tnx_id`, `tnx_type`, and `wallet_id`.
• Constraints: Primary keys ensure uniqueness, and check constraints on `tbl_system_peers` validate `last_time` and `port` values.
• Query Store: Enabled to track query performance and identify bottlenecks.

CREATE TABLE tbl_optimize_row_counts (
    table_name VARCHAR(30) NOT NULL,
    row_count BIGINT NOT NULL DEFAULT (0),
    last_updated DATETIME NOT NULL DEFAULT (GETDATE()),
    CONSTRAINT PK_tbl_row_counts PRIMARY KEY (table_name)
)
                

References:
Indexes (Microsoft Docs)
Performance Monitoring (Microsoft Docs)

Step 8: Data Flow and Relationships

The data flow in the TallyBox database follows a structured process:

1. Incoming transactions are stored in one of the sharded `tbl_order_dispatcher_X` tables (0-9), identified by `the_sign`.
2. The `timer_engine_Tick` process moves transactions to `tbl_order_ods` for processing.
3. The `sql_ods_processor` validates transactions using `tbl_system_currency`, `tbl_system_graph`, and `tbl_tallybox_wallet`, then updates `tbl_tallybox_book` and `tbl_tallybox_sign`.
4. For group transactions, `tbl_order_ods_multiple` stores multiple recipients linked by `the_sign_md5`.
5. Periodically, `timer_buffer_Tick` moves records from buffer tables (`tbl_tallybox_wallet_buffer`, etc.) to archive tables (`tbl_tallybox_wallet_archive_1`, etc.) based on `archive_id`.
6. `tbl_system_treasury` and `tbl_system_gazette` log treasury and blockchain-related data.

• `tbl_tallybox_book.wallet_id` references `tbl_tallybox_wallet.wallet_id`.
• `tbl_tallybox_book.currency_id` references `tbl_system_currency.currency_id`.
• `tbl_tallybox_sign.tnx_id` links to `tbl_tallybox_book.tnx_id`.
• `tbl_order_ods.the_sign` links to `tbl_tallybox_sign.the_sign`.

BEGIN
    SELECT * FROM tbl_order_dispatcher_X WHERE the_sign = 'signature'
    INSERT INTO tbl_order_ods SELECT * FROM tbl_order_dispatcher_X
    EXEC sql_ods_processor @the_sign
        VALIDATE wallet_from, wallet_to IN tbl_tallybox_wallet
        VALIDATE order_currency IN tbl_system_currency
        INSERT INTO tbl_tallybox_book (tnx_id, wallet_id, currency_amount, tally_hash)
        INSERT INTO tbl_tallybox_sign (tnx_id, the_sign, the_sign_md5)
    MOVE TO tbl_tallybox_book_buffer WHERE archive_id = 1
    EXEC timer_buffer_Tick
        INSERT INTO tbl_tallybox_book_archive_1 SELECT * FROM tbl_tallybox_book_buffer
END
                

References:
Stored Procedures (Microsoft Docs)
Database Design (Wikipedia)

Conclusion

The `net_mixoftix_tallybox` database is designed for efficient transaction processing, wallet management, and data archiving in the TallyBox application. Its sharded dispatcher tables, system configuration tables, ledger and wallet tables, and buffer/archive system ensure scalability and performance. Non-clustered indexes and the Query Store optimize query performance, while constraints maintain data integrity. By following this tutorial, developers can set up the database, understand its structure, and integrate it with the TallyBox application.

For further assistance, refer to the provided SQL script and Microsoft SQL Server documentation. If you have questions, contact the TallyBox support team.

References:
SQL Server Documentation
Blockchain (Wikipedia)