4.1 Choose a Table Type

Choose a table type that matches the data early in design. An unsuitable type can reduce performance and limit required functionality.

• Selection Guide
• Type Comparison
• TRANSACTION vs LOOKUP

For table roles and storage concepts, see Data Model Concepts. The same data can need different types depending on whether you accumulate history or update current state. Review mutation, query, and persistence requirements together.

Describe the data before selecting a type

Before naming a table, describe the fact represented by one row in a sentence. Even for the same equipment, one temperature reading, one current operating state, and one maintenance job have different row semantics, keys, and update patterns.

For example, consider TAG for temperature history, LOG for alarms, and LOOKUP for equipment code tables. If inventory changes and job registration must commit together, consider TRANSACTION in Standard Edition. A current-state cache can use VOLATILE if it is rebuildable from source data. Align row semantics and failure recovery before combining these records in one table.

Selection guide

Use the following flow to narrow candidates, then verify DML, transactions, memory usage, and edition support in the comparison tables. Even reference data may need TRANSACTION instead of LOOKUP when multiple changes must share one transaction.

Decision flow

Is the data sensor/device measurements?
  ├── YES → Time axis?     YES → TAG TABLE (BASETIME)
  │         Distance axis? YES → TAG TABLE (BASEDISTANCE)
  └── NO  ↓

Is the data events/logs/packets? (append-only)
  ├── YES → LOG TABLE
  └── NO  ↓

Is the data a code table/reference data? (repeated reads and updates)
  ├── YES → LOOKUP TABLE
  └── NO  ↓

Is the data in-memory state/cache that can be discarded on server restart?
  ├── YES → VOLATILE TABLE
  └── NO  ↓

General relational business data (UPDATE/DELETE/SELECT/INSERT all required)
  └── TRANSACTION TABLE

Key criteria

Considerations

• Using a unique tag name for every event causes continuing growth in tags and metadata. Consider LOG for events without recurring measurement targets.
• LOG cannot UPDATE or DELETE with general predicates, so it is unsuitable for mutable data. Use retention-oriented BEFORE, OLDEST, or EXCEPT DELETE.
• TRANSACTION is Standard Edition only. In Cluster Edition, consider LOOKUP for small datasets or an external RDBMS.
• VOLATILE data is lost on server restart.

Type comparison

Feature comparison

Append API support depends on the SDK and ingestion path as well as the table type. Check your driver/table combination in the SDK Append Matrix.

Storage characteristics

Small in-memory tables do not have a fixed row-count definition. Measure actual memory including row width, variable-length values, and secondary indexes. Compression ratios and server specifications alone cannot guarantee disk-table throughput either; run representative ingestion and queries together.

TRANSACTION table restrictions

TRANSACTION tables have these restrictions:

• No Cluster Edition support: Standard Edition only.
• Minimum columns: At least one.

TRANSACTION vs LOOKUP

TRANSACTION and LOOKUP both store relational data, but differ in target scale and features.

Comparison

Selection criteria

Choose TRANSACTION for:

• Data requiring explicit transactions and relational DML
• General relational workloads requiring UPDATE, DELETE, INSERT, and SELECT
• Queries on varied column combinations without a PRIMARY KEY
• Standard Edition environments

Choose LOOKUP for:

• Code tables and reference data
• PRIMARY KEY lookups and single-row UPDATE/DELETE
• Use in Cluster Edition
• Real-time reference-data updates

Example

-- LOOKUP: country codes (PK lookup and predicate-based UPDATE)
CREATE LOOKUP TABLE country_code (
    code   VARCHAR(4)   PRIMARY KEY,
    name   VARCHAR(64)
);
INSERT INTO country_code VALUES ('KR', 'Republic of Korea');
UPDATE country_code SET name = 'Korea' WHERE code = 'KR';
SELECT code, name FROM country_code WHERE code = 'KR';

-- TRANSACTION: order history (large scale, general UPDATE/DELETE)
CREATE TRANSACTION TABLE order_history (
    order_id  LONG PRIMARY KEY,
    item_id   INTEGER,
    qty       INTEGER,
    amount    DECIMAL(18,2)
);
INSERT INTO order_history VALUES (12345, 501, 1, 12000.00);
UPDATE order_history SET qty = 10 WHERE order_id = 12345;
SELECT order_id, qty, amount FROM order_history WHERE order_id = 12345;
DELETE FROM order_history WHERE order_id = 12345;
SELECT COUNT(*) FROM order_history WHERE order_id = 12345;

The first SELECT returns the updated country name; the order SELECT returns quantity 10. The final COUNT is 0. Here, amount is an illustrative value maintained separately from quantity and is not recalculated automatically. In an actual order model, define the relationship among unit price, quantity, and total, and which columns change together. When finished, use DROP TABLE to remove only the tables created in this example.