# 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 the current live DB there are 307 virtual-table master rows in
`gdsconfigtbmaster` and 14,385 virtual-column rows in
`gdsconfigtbslave`. 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

In the current live DB, all 307 `gdsconfigtbmaster` rows have a non-empty
`sTbName`, but 11 of those names do **not** resolve to a current object in
`information_schema.tables`. So the safe statement is: the metadata expects
an underlying SQL object, but the live schema is not perfectly aligned for
every virtual-table row.

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