# 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.

Virtual tables cover lookup tables, classification trees, and
configurable parameter sets. The catalog grows freely as PMs add new
shapes.

## 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

Every `gdsconfigtbmaster` row carries a non-empty `sTbName`, but in
practice some of those names may not resolve to a current object in
`information_schema.tables` — schema migrations and renames happen
faster than the metadata is cleaned up. So the safe statement is: the
metadata *expects* an underlying SQL object, but a deployed schema is
not always perfectly aligned for every virtual-table row. An audit
script that diffs `gdsconfigtbmaster.sTbName` against
`information_schema.tables` is the cleanest way to surface drift.

## 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` and walk through its master row +
slave rows. A future revision
of the wiki should add this.