9.2 Table Structure and Schema

This section covers LOOKUP table structure and schema design.

LOOKUP Table Design

LOOKUP tables store code lists and reference data. A PRIMARY KEY identifies each row. They support UPDATE/DELETE by primary key or general predicates and store data persistently on disk.

Persistent Storage and In-Memory Queries

LOOKUP uses two layers to provide persistence and in-memory query performance.

1. Modified rows are written to persistent storage so they survive restarts.
2. At startup, the server reads all LOOKUP rows from persistent storage and reconstructs in-memory rows containing all column values.
3. Each in-memory row is registered in the mandatory PRIMARY KEY red-black index.
4. SQL queries use the reconstructed in-memory rows and indexes.

Conceptually, each row can be viewed as a key-value entry.

PRIMARY KEY                       Other column values
sensor_id = 'TEMP-01'  ───────►  { site, unit, status, ... }
          key                              value

This explains why LOOKUP is particularly suitable for PRIMARY KEY lookups while also providing a SQL table interface, general predicate queries, JOIN, and secondary indexes. Persistent storage does not mean that only requested rows are fetched from disk during queries. All rows and red-black secondary indexes consume memory. Consider variable-length columns, JSON values, and secondary index sizes as well as row counts when designing the schema.

• Use Cases
• PRIMARY KEY Design
• Column and Sequence Design
• JSON Columns and Queries
• Reference Design Patterns
• Index Strategy
• PRIMARY KEY Policy
• UPDATE/DELETE with General Predicates
• Backup and Recovery Support
• Limitations and Considerations