# How to define a virtual table

A *virtual table* in xly is a "table" declared as metadata, not as DDL.
The framework knows about it because rows in `gdsconfigtbmaster` and
`gdsconfigtbslave` describe its shape; the actual data is stored in a
real physical table, but the *abstraction* the framework operates over
is the metadata declaration.

This is distinct from a database **view**, which is a `CREATE VIEW`
SQL object. Both can back a form via `gdsconfigformmaster.sType =
'table'` or `'view'`.

## What virtual tables are for

- Letting a PM declare "I need a thing of this shape" without an
  engineer running `CREATE TABLE`.
- Defining the shape that downstream forms can layer on top of.
- Centralising tenant-aware column definitions so multiple forms
  reading the same shape can share defaults.

In this dev DB there are 307 virtual tables (vs. 901 physical base
tables). They cover lookup tables, classification trees, and
configurable parameter sets.

## Recipe

### 1. The virtual-table master — `gdsconfigtbmaster`

One row per virtual table:

| Column | Value |
|---|---|
| `sId` | unique virtual-table ID |
| `sName` | the virtual-table's logical name |
| `sChinese` / `sEnglish` / `sBig5` | display name |
| `sBrandsId` / `sSubsidiaryId` | tenant scope |
| `sTbName` | the underlying physical table name (if backed by one) |
| (other configuration columns describing storage and indexing) |

### 2. The columns — `gdsconfigtbslave`

One row per column. Each row carries the column's name, type, default,
display label, validation, and whether it's part of the primary key.

## Open: what backs the data

The wiki's verification list includes confirming exactly how a virtual
table's rows are persisted: do they always back to a pre-existing
physical table named in `sTbName`, or does the framework create one on
the fly? In this dev DB, every virtual table's `sTbName` resolves to a
real table. The "create on the fly" path may not be used in practice.

## When to choose virtual table vs. view vs. table

| Need | Choose |
|---|---|
| A new shape, declared by a PM, backed by a real (possibly pre-existing) table | virtual table |
| A read-only join across existing tables | a database view |
| A new shape that genuinely needs storage no existing table provides | a real `CREATE TABLE` (engineer task) |

The virtual-table channel is the framework's "type system" for
data-driven shapes; the physical schema is what actually stores rows.
The two are deliberately decoupled.

## Worked example

This page would benefit from a concrete worked example — pick a real
virtual table from `gdsconfigtbmaster` (e.g., one of the 307 in this
dev DB) and walk through its master row + slave rows. A future revision
of the wiki should add this.