Reference

Takes its value from a column generated in an earlier table, for a foreign key between tables.

Use it to link the tables of a data set, such as orders that reference customers and order lines that reference orders. Every value is copied from the referenced table's generated column, so every reference matches a row, and a join across the generated tables returns every row.

The referenced table must be listed before this table in the request's tables list. rule sets how the referenced values are distributed across the rows, such as many-to-one or one-to-one.

Sub-fields: value, or left and right together. See Sub-fields.

Every JSON block on this page is a single field object, one entry in a table's fields array, unless it is a full request with a tables list. See Building a Request for the full request structure.

Sub-fields

Sub-field	Produces
`left`	The left side of a bridge-table pair, from `targetColumn` in `targetTable`. Requires `right`. Cannot be combined with `value`. See Bridge tables.
`right`	The right side of a bridge-table pair, from `rightTargetColumn` in `rightTargetTable`. Requires `left`. Cannot be combined with `value`.
`value`	The referenced value, from `targetColumn` in `targetTable`. Cannot be combined with `left` or `right`.
Any other column of `targetTable`	That column's value from the same referenced row as `value`, mapped under the column's own name. Requires `value`. See Copying other columns of the referenced row.

Map each sub-field to the same dataType as the column it references. A text type such as VarChar is also accepted, and a whole-number column can be referenced by another whole-number type, such as an Integer column by a Long column. Any other pair of types is rejected before any data is generated.

The referenced column can be generated by any field type, such as Number, Identifier or String. A row whose referenced value is null is never referenced.

Properties

Property	Type	Default	Description
`distinct`	`boolean`	`false`	Whether the value must differ from the value of every earlier `Reference` field of the same table that references the same column. See Distinct values across fields.
`foreignKey`	`string[]?`	none	The columns of `targetTable` that the foreign key in the generated schema is on, in key order. Each one must be copied by this field to a column that is not hidden. Without it, the key is on `value` alone. See Keys in the generated schema.
`map`	`object`	required	Maps `value`, or `left` and `right`, to destination columns. With `value`, a key with the name of another column of `targetTable` copies that column from the same row. See Building a Request.
`nullPercentageMax`	`number`	`0`	Upper bound (0-100) of the percentage of rows that should be null. See `nullPercentageMin`.
`nullPercentageMin`	`number`	`0`	Lower bound (0-100) of the percentage of rows that should be null. The rate is fixed once for the whole field from this range, not recalculated per row. A null row references no row. Set equal to `nullPercentageMax` for an exact rate.
`rightTargetColumn`	`string?`	none	The referenced column for `right`. Required for a bridge table, and rejected otherwise.
`rightTargetTable`	`string?`	none	The referenced table for `right`. Required for a bridge table, and rejected otherwise.
`rightWeightColumn`	`string?`	none	The `weightColumn` of the right side of a bridge table, in `rightTargetTable`.
`rightWeights`	`ReferenceWeight[]?`	none	The `weights` of the right side of a bridge table.
`rule`	`ReferenceRule`	`"Random"`	How the referenced values are distributed across the rows.
`seed`	`integer`	random	Per-field seed, overriding the request's own `seed` for this field only.
`stdDev`	`number?`	none	The standard deviation of the number of rows that reference each value, in rows. Required by `Clustered` and `ClusteredDistributed`, must be greater than 0, and is rejected by every other rule. See Clustered rules.
`targetColumn`	`string`	required	The referenced column, for `value` or `left`.
`targetTable`	`string`	required	The referenced table, for `value` or `left`.
`unique`	`boolean`	`false`	Whether every row must have a different value, or a different pair for a bridge table. See Unique values.
`weightColumn`	`string?`	none	A column of `targetTable` whose values set how often each row is referenced. Requires `weights`. See Weighting by another column.
`weights`	`ReferenceWeight[]?`	none	The weight for each value of `weightColumn`. Requires `weightColumn`.

ReferenceRule

Rule	Effect
`Random`	Each row references a row of `targetTable` selected at random. One customer can have any number of orders, and some have none. This is a many-to-one relationship.
`Distributed`	The referenced rows are used in turn, in a random order, so every row of `targetTable` is referenced the same number of times, or one more.
`Unique`	Each referenced value is used at most once, for a one-to-one relationship. The referenced column must have at least as many distinct values as this table has rows, or the request is rejected.
`Clustered`	The number of rows that reference each value has a normal distribution around the average. Some values are referenced more than the average, some less, and some not at all. See Clustered rules.
`ClusteredDistributed`	The same as `Clustered`, except every value is referenced at least once. Requires at least as many rows as there are values to reference.

Only Random can be weighted, because each of the other rules sets how many times every value is used.

With Unique, a value that the referenced column repeats is used only once. For a bridge table, Unique applies to each side, so each left value and each right value is used at most once.

Clustered rules

With Random, the number of orders for each customer varies by chance. With Distributed, every customer has the same number of orders. Clustered sets how much the number varies with stdDev. The average is the number of rows divided by the number of referenced values.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rule": "Clustered",
  "stdDev": 2,
  "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
}

For 50,000 orders and 1,000 customers, the average is 50 orders per customer. The number of orders per customer:

Rule	`stdDev`	Fewest orders	Most orders	Customers with 46 to 54 orders	Customers with no orders
`Random`		30	71	496	0
`Distributed`		50	50	1000	0
`Clustered`	2	42	56	977	0
`Clustered`	12	1	88	272	0
`Clustered`	40	0	176	83	62
`ClusteredDistributed`	40	1	175	82	0

stdDev is a number of rows, not a percentage, so the same stdDev gives the same spread for any table sizes. About 95% of the values are referenced within two standard deviations of the average.

A stdDev large enough to reach zero leaves some values unreferenced. ClusteredDistributed references every value at least once:

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rule": "ClusteredDistributed",
  "stdDev": 40,
  "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
}

ClusteredDistributed requires at least as many rows as there are values to reference, and a request with fewer rows is rejected.

The rows that reference each value are spread across the table, so consecutive rows rarely reference the same value and the column is not sorted by the value it references. The total of the planned counts is the number of rows, so every row references a value.

Weighting by another column

The key values of the referenced table do not exist until that table is generated, so a request cannot weight them directly. weightColumn weights the referenced rows by another column of the same table, such as a status, a category or a color. weights sets a weight for each value of that column:

{
  "type": "Reference",
  "targetTable": "product",
  "targetColumn": "product_id",
  "weightColumn": "color",
  "weights": [
    { "value": "Blue", "weight": 4 },
    { "value": "Red", "weight": 1 },
    { "value": "Green", "weight": 1 },
    { "value": "Yellow", "weight": 2 }
  ],
  "map": { "value": { "column": "product_id", "dataType": "Integer" } }
}

A product with a color of Blue is four times as likely to be referenced as a red or green product, and twice as likely as a yellow product.

A referenced row whose weightColumn value is not in weights is never referenced, so the list also filters the rows. Without Green in the list, no green product is referenced.

weightColumn and weights must be set together, apply only to the Random rule, and are rejected when no row of the referenced table has a value in the list.

See A weighted foreign key for a full request with the generated tables and schema.

ReferenceWeight

Property	Type	Default	Description
`value`	`string`	required	A value of the weight column. Compared as text, ignoring case. Each value can be listed once.
`weight`	`number`	`1`	The weight of the rows with that value, relative to the other entries. Must be greater than 0.

Weights are relative, not percentages, so they do not need to total any particular number. 4 and 2 give the same distribution as 40 and 20.

Bridge tables

A many-to-many relationship, such as customers and the products they have bought, is stored in a bridge table. Each row of a bridge table pairs one row of one table with one row of another. A customer can appear with many products, and a product with many customers.

Map left and right instead of value. left takes its values from targetColumn in targetTable, and right takes its values from rightTargetColumn in rightTargetTable. Both tables must be listed before the bridge table in the request.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rightTargetTable": "product",
  "rightTargetColumn": "product_id",
  "unique": true,
  "map": {
    "left": { "column": "customer_id", "dataType": "Integer" },
    "right": { "column": "product_id", "dataType": "Integer" }
  }
}

With these two tables, customer and product:

customer_id	segment
1	Business
2	Retail
3	Business
4	Business
5	Business

product_id	color
100	Blue
101	Red
102	Blue
103	Red

A bridge table, customer_product, of 8 rows:

customer_id	product_id
4	102
3	100
2	101
3	102
4	103
4	100
3	101
5	101

Every customer_id is a customer and every product_id is a product, so every row joins to both tables. Customers 3 and 4 have three products each, customers 2 and 5 have one each, and customer 1 has none. Product 101 appears three times, and product 103 once.

How each pair is chosen

Each row takes one value from each side. The two values are chosen separately, each from its own table, by the same rule. So rule sets how often each customer appears and, separately, how often each product appears. See Rules on a bridge table.

unique applies to the pair. A customer and a product can each appear many times, but never together twice. The number of possible pairs is the number of distinct left values multiplied by the number of distinct right values, so 5 customers and 4 products allow at most 20 unique rows. A request for more is rejected. Two separate Reference fields that are each unique can still repeat a pair.

Rules on a bridge table

Rule	How often each value of a side is used	Pairs	With `unique`
`Random`	Chosen at random, so some values are used more than others and some not at all. Can be weighted. See Weighting each side.	A pair can repeat.	A pair that has been used is replaced by another random pair.
`Distributed`	Every value is used the same number of times, or one more.	No pair repeats until every possible pair has been used.	Every row is a different pair, up to the number of possible pairs.
`Unique`	Every value is used at most once. Each side must have at least as many distinct values as the table has rows.	Every pair is different, and each value is in one pair at most.	No further effect.
`Clustered`	The number of times each value is used has a normal distribution around that side's average, with `stdDev`. Some values are used more than the average, some less, and some not at all.	A pair can repeat.	A pair that has been used is replaced by moving the right side to a value planned for a later row. See Clustered rules on a bridge table.
`ClusteredDistributed`	The same as `Clustered`, except every value is used at least once. The table must have at least as many rows as each side has values.	A pair can repeat.	The same as `Clustered`.

Each side's average is the number of rows divided by the number of values on that side. With 600 rows, 100 customers and 40 products, the average is 6 rows for each customer and 15 for each product.

Distributed on a bridge table

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rightTargetTable": "product",
  "rightTargetColumn": "product_id",
  "rule": "Distributed",
  "map": {
    "left": { "column": "customer_id", "dataType": "Integer" },
    "right": { "column": "product_id", "dataType": "Integer" }
  }
}

With the same customer and product tables, 10 rows:

customer_id	product_id
5	102
1	100
2	103
3	101
4	102
5	100
1	103
2	101
3	102
4	100

Every customer appears twice. Products 100 and 102 appear three times and products 101 and 103 twice, because 10 rows do not divide evenly across 4 products. No pair repeats. With 20 rows, every customer appears four times, every product five times, and every one of the 20 possible pairs appears once.

Unique on a bridge table

With "rule": "Unique" and the same two tables, 4 rows:

customer_id	product_id
5	102
1	100
2	103
3	101

Each customer and each product is in one row at most, which pairs them one to one. Customer 4 is not used, because there are more customers than rows. The table can have at most 4 rows, because the product table has 4 values.

Clustered rules on a bridge table

stdDev applies to both sides and is a number of rows, as it is for a reference that maps value. See Clustered rules. The number of times each customer and each product is used, for 200 rows, 20 customers and 10 products. The average is 10 rows for each customer and 20 for each product.

Rule	`stdDev`	Fewest per customer	Most per customer	Customers not used	Fewest per product	Most per product
`Random`		4	16	0	13	28
`Distributed`		10	10	0	20	20
`Clustered`	3	6	15	0	17	25
`Clustered`	15	0	34	1	1	60
`ClusteredDistributed`	15	1	33	0	3	40

The same stdDev is a larger share of a smaller average, so it spreads the side with more values further.

With unique, a value can appear at most as many times as the other side has distinct values. A customer planned for 12 rows cannot have 12 different products when there are only 10. A stdDev that plans more than that for some value, or a row count close to the number of possible pairs, is rejected during generation. When a planned pair has already been used, the right side moves to the value planned for a later row, so a right value can be used once more or once less than its planned number.

Weighting each side

With the Random rule, weightColumn and weights weight the left side, and rightWeightColumn and rightWeights weight the right side. Each side is weighted by a column of its own table. See Weighting by another column.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "weightColumn": "segment",
  "weights": [
    { "value": "Business", "weight": 3 },
    { "value": "Retail", "weight": 1 }
  ],
  "rightTargetTable": "product",
  "rightTargetColumn": "product_id",
  "rightWeightColumn": "color",
  "rightWeights": [
    { "value": "Blue" }
  ],
  "map": {
    "left": { "column": "customer_id", "dataType": "Integer" },
    "right": { "column": "product_id", "dataType": "Integer" }
  }
}

For 2,000 rows with 20 customers, 11 of them Business and 9 Retail, and 10 products, 4 of them Blue and 6 Red:

Side	Value	Rows
Customer `segment`	`Business`	1553
Customer `segment`	`Retail`	447
Product `color`	`Blue`	2000
Product `color`	`Red`	0

Each Business customer is three times as likely to appear as each Retail customer. Red is not in rightWeights, so no red product appears.

Foreign keys on a bridge table

A bridge table always has two foreign keys in the generated schema. One is from the left column to targetColumn in targetTable, and the other is from the right column to rightTargetColumn in rightTargetTable. Declare the two columns together in the bridge table's primaryKey to give it a two-column primary key. See A bridge table with both keys declared for a full request with its schema.

What a bridge table does not support

A bridge table maps only left and right. It cannot copy other columns of the referenced rows, and it does not support distinct. The foreignKey property does not apply either. That property places a reference's foreign key on several columns of the referenced table, which a bridge table does not copy. Each side of a bridge table has its own foreign key, as described above.

Copying other columns of the referenced row

Each row references one row of the referenced table. value is targetColumn from that row. Map another column of the referenced table under its own name to copy it from the same row:

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "map": {
    "value": { "column": "customer_id", "dataType": "Integer" },
    "region": { "column": "customer_region", "dataType": "VarChar" }
  }
}

With a customer table that has a region column:

customer_id	region
1	East
2	North
3	West
4	South
5	East
6	West

The first rows of the referencing table. Each customer_region is the region of the customer in the same row:

order_id	customer_id	customer_region
1	50	West
2	45	East
3	39	North
4	11	South
5	6	West
6	15	North

A copied column has the same type rules as value, and a column that the referenced table does not generate is rejected. A copied column can be hidden, so that a later Calculated field can use it without it being written.

rule, weightColumn and weights select rows in the same way with or without copied columns. unique and the Unique rule compare everything the field writes, so two referenced rows with the same targetColumn value and a different copied value are different.

A bridge table maps only left and right, and cannot copy other columns.

Two separate Reference fields select their rows independently. A customer_id from one field and a customer_region from another are from different customers, so copy both through one field.

Distinct values across fields

Two Reference fields of one table select their values separately, so a row can have the same value in both, such as an order with the same customer as its buyer and its recipient. distinct makes the value differ from the value of every earlier Reference field of the same table that references the same targetColumn of the same targetTable:

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "distinct": true,
  "map": { "value": { "column": "recipient_id", "dataType": "Integer" } }
}

A field is compared only with the fields declared before it. For three fields that must all differ, set distinct on the second and on the third. A row where an earlier field has no value, because of its nullPercentageMin and nullPercentageMax, is not compared.

A row that would repeat a value uses the next row in the field's order instead. With Distributed, Clustered and ClusteredDistributed, the number of rows that reference each value can therefore differ from the planned number by the rows that repeated. With weightColumn, only rows that weights includes are used.

A distinct field is rejected when:

No earlier Reference field in the table references the same targetColumn of the same targetTable.
It is a bridge table, which maps left and right instead of value.
Its rule is Unique, which already uses each value at most once.
The referenced column has no more distinct values than the number of earlier fields it differs from.

See Different customers in one row for an example.

Unique values

With unique set, every row has a different value, or a different pair for a bridge table. With copied columns, every row has a different combination of the values the field writes. The number of different values is counted when the referenced table has been generated, and a request for more rows than that is rejected before this table is generated:

This field (table 'order', column 'customer_id') is unique for 8 rows, but its settings allow only 5 distinct value(s). Lower rowCount, widen the field's range, or turn off unique.

unique with the Random rule selects values at random and skips a repeated one. The Unique rule uses each value once by design.

Keys in the generated schema

The generated schema declares the keys between the tables, so the generated data includes its constraints.

Every Reference field adds a foreign key, from the field's own targets, with nothing else in the request. The key is on value, or on left and on right separately for a bridge table. Copied columns are not part of it.

When the referenced table's key has more than one column, list them in foreignKey for one key on all of them. Each listed column must be copied by the field, as targetColumn through value or under its own name, and must not be hidden:

{
  "type": "Reference",
  "targetTable": "account",
  "targetColumn": "account_no",
  "foreignKey": ["region", "account_no"],
  "map": {
    "value": { "column": "account_no", "dataType": "Integer" },
    "region": { "column": "account_region", "dataType": "Integer" }
  }
}

Sample output, with an account table that has a primary key on region and account_no:

account_no	account_region
1021	1
1013	2
1012	3
1010	2
1005	3
1023	2

The generated schema declares the foreign key fk_payment_account_region_account_no, from account_region and account_no to region and account_no in account.

A table declares its primary key with primaryKey, a list of column names in key order:

{
  "name": "customer",
  "rowCount": 500,
  "primaryKey": ["customer_id"],
  "fields": [ ... ]
}

primaryKey is set on the table rather than on a field, so a key can have more than one column, as a bridge table needs:

{
  "name": "customer_product",
  "rowCount": 2000,
  "primaryKey": ["customer_id", "product_id"],
  "fields": [ ... ]
}

A column in primaryKey must be generated by the table and must not be hidden. A key column is never null, so the generated schema declares it not null. The field that writes a key column cannot set nullPercentageMin or nullPercentageMax above 0, and a key column that comes out empty in any row is rejected. The generated values are also checked, and a repeated key is rejected:

Table 'customer' declares a primary key on customer_id, but the generated data repeats a value. Mark the field unique, or use a field type that produces distinct values.

Set unique on the field, or use a field that produces distinct values, such as Number in Sequential mode.

Examples

The first example is a full request with a customer table of 50 rows. The field examples after it reference the same customer table.

Many orders for each customer

The default Random rule. A customer can be on any number of orders, and some are on none.

{
  "seed": 42,
  "tables": [
    {
      "name": "customer",
      "rowCount": 50,
      "primaryKey": ["customer_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "unique": true,
          "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
        }
      ]
    },
    {
      "name": "order",
      "rowCount": 500,
      "primaryKey": ["order_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "unique": true,
          "map": { "value": { "column": "order_id", "dataType": "Integer" } }
        },
        {
          "type": "Reference",
          "targetTable": "customer",
          "targetColumn": "customer_id",
          "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
        }
      ]
    }
  ]
}

Sample output, the first rows of order:

order_id	customer_id
1	50
2	45
3	39
4	11
5	6
6	15

Every customer referenced equally

Distributed uses every customer in turn, so 500 orders for 50 customers gives each customer exactly ten orders.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rule": "Distributed",
  "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
}

Sample output, the first rows of order:

order_id	customer_id
1	2
2	35
3	9
4	38
5	23
6	26

A one-to-one relationship

Unique uses each customer at most once. With 20 rows, 20 of the 50 customers are referenced, each once. A table with more rows than the customer table has distinct values is rejected.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rule": "Unique",
  "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
}

Sample output, the first rows of a 20-row table:

order_id	customer_id
1	2
2	35
3	9
4	38
5	23
6	26

An optional relationship

A null row references no customer, for a foreign key that can be empty.

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "nullPercentageMin": 15,
  "nullPercentageMax": 15,
  "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
}

Sample output:

order_id	customer_id
1	15
2	35
3	18
4	43
5	11
6

Different customers in one row

An order table with a buyer and a recipient, both references to customer. The request is the same with and without distinct on recipient_id:

{
  "seed": 2,
  "tables": [
    {
      "name": "customer",
      "rowCount": 5,
      "primaryKey": ["customer_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
        }
      ]
    },
    {
      "name": "order",
      "rowCount": 8,
      "primaryKey": ["order_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "map": { "value": { "column": "order_id", "dataType": "Integer" } }
        },
        {
          "type": "Reference",
          "targetTable": "customer",
          "targetColumn": "customer_id",
          "map": { "value": { "column": "buyer_id", "dataType": "Integer" } }
        },
        {
          "type": "Reference",
          "targetTable": "customer",
          "targetColumn": "customer_id",
          "distinct": true,
          "map": { "value": { "column": "recipient_id", "dataType": "Integer" } }
        }
      ]
    }
  ]
}

The rows of order with distinct:

order_id	buyer_id	recipient_id
1	2	4
2	3	1
3	5	3
4	2	4
5	3	4
6	2	3
7	5	1
8	3	2

The rows with "distinct": true removed from recipient_id:

order_id	buyer_id	recipient_id
1	2	4
2	3	1
3	5	3
4	2	4
5	3	4
6	2	2
7	5	5
8	3	2

Orders 6 and 7 have the same customer as buyer and recipient without distinct. With it, those two orders have a different recipient and the other rows are unchanged.

A weighted foreign key

A product table with a primary key and a color column, and a sale table whose reference is weighted by color. Blue products are four times as likely to be referenced as red or green products, and twice as likely as yellow products.

{
  "seed": 42,
  "tables": [
    {
      "name": "product",
      "rowCount": 4,
      "primaryKey": ["product_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 100,
          "unique": true,
          "map": { "value": { "column": "product_id", "dataType": "Integer" } }
        },
        {
          "type": "String",
          "mode": "Lookup",
          "unique": true,
          "lookupValues": [
            { "value": "Blue" },
            { "value": "Red" },
            { "value": "Green" },
            { "value": "Yellow" }
          ],
          "map": { "value": { "column": "color", "dataType": "VarChar" } }
        }
      ]
    },
    {
      "name": "sale",
      "rowCount": 1000,
      "primaryKey": ["sale_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "unique": true,
          "map": { "value": { "column": "sale_id", "dataType": "Integer" } }
        },
        {
          "type": "Reference",
          "targetTable": "product",
          "targetColumn": "product_id",
          "weightColumn": "color",
          "weights": [
            { "value": "Blue", "weight": 4 },
            { "value": "Red", "weight": 1 },
            { "value": "Green", "weight": 1 },
            { "value": "Yellow", "weight": 2 }
          ],
          "map": { "value": { "column": "product_id", "dataType": "Integer" } }
        }
      ]
    }
  ]
}

product is generated first:

product_id	color
100	Blue
101	Green
102	Red
103	Yellow

The first rows of sale:

sale_id	product_id
1	103
2	102
3	103
4	100
5	103
6	100

The number of sales for each product, across all 1,000 rows:

product_id	color	Weight	Rows	Share of sales
100	Blue	4	515	51.5%
103	Yellow	2	237	23.7%
102	Red	1	124	12.4%
101	Green	1	124	12.4%

The weights total 8, so the expected shares are one half for blue, one quarter for yellow, and one eighth each for red and green. Each row is selected separately, so the shares are approximate, and a smaller table is less exact.

The request contains no product id. It weights by color, which is known before product is generated.

The generated schema declares the primary keys of both tables and the foreign key between them:

{
  "name": "dataset",
  "tables": [
    {
      "name": "product",
      "columns": [
        { "name": "product_id", "type": "integer", "notNull": true },
        { "name": "color", "type": "varchar", "size": 6 }
      ],
      "constraints": [
        {
          "type": "primarykey",
          "name": "pk_product",
          "primaryKey": { "keyColumns": ["product_id"] }
        }
      ]
    },
    {
      "name": "sale",
      "columns": [
        { "name": "sale_id", "type": "integer", "notNull": true },
        { "name": "product_id", "type": "integer" }
      ],
      "constraints": [
        {
          "type": "primarykey",
          "name": "pk_sale",
          "primaryKey": { "keyColumns": ["sale_id"] }
        },
        {
          "type": "foreignkey",
          "name": "fk_sale_product_id",
          "foreignKey": {
            "deleteRule": "noaction",
            "updateRule": "noaction",
            "sourceColumns": ["product_id"],
            "lookupSchema": "dataset",
            "lookupTable": "product",
            "lookupColumns": ["product_id"]
          }
        }
      ]
    }
  ]
}

pk_product and pk_sale are from the primaryKey of each table. fk_sale_product_id is from the Reference field. lookupSchema is the name of the schema, which is schemaName when the request sets one.

A bridge table with both keys declared

A customer table and a product table, and a bridge table that pairs them. Each parent table has a column to weight by, segment and color, which the variations below use.

{
  "seed": 42,
  "tables": [
    {
      "name": "customer",
      "rowCount": 100,
      "primaryKey": ["customer_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 1,
          "unique": true,
          "map": { "value": { "column": "customer_id", "dataType": "Integer" } }
        },
        {
          "type": "String",
          "mode": "Lookup",
          "lookupValues": [{ "value": "Retail" }, { "value": "Business" }],
          "map": { "value": { "column": "segment", "dataType": "VarChar" } }
        }
      ]
    },
    {
      "name": "product",
      "rowCount": 40,
      "primaryKey": ["product_id"],
      "fields": [
        {
          "type": "Number",
          "mode": "Sequential",
          "start": 100,
          "unique": true,
          "map": { "value": { "column": "product_id", "dataType": "Integer" } }
        },
        {
          "type": "String",
          "mode": "Lookup",
          "lookupValues": [{ "value": "Blue" }, { "value": "Red" }, { "value": "Green" }],
          "map": { "value": { "column": "color", "dataType": "VarChar" } }
        }
      ]
    },
    {
      "name": "customer_product",
      "rowCount": 600,
      "primaryKey": ["customer_id", "product_id"],
      "fields": [
        {
          "type": "Reference",
          "targetTable": "customer",
          "targetColumn": "customer_id",
          "rightTargetTable": "product",
          "rightTargetColumn": "product_id",
          "rule": "Random",
          "unique": true,
          "map": {
            "left": { "column": "customer_id", "dataType": "Integer" },
            "right": { "column": "product_id", "dataType": "Integer" }
          }
        }
      ]
    }
  ]
}

Sample output, the first rows of customer:

customer_id	segment
1	Business
2	Retail
3	Business
4	Business
5	Business
6	Business

The first rows of product:

product_id	color
100	Blue
101	Red
102	Blue
103	Green
104	Blue
105	Blue

The first rows of customer_product:

customer_id	product_id
70	125
42	107
39	119
46	129
70	130
48	123

The generated schema declares pk_customer, pk_product, a two-column pk_customer_product, and a foreign key from each bridge column to its table, fk_customer_product_customer_id and fk_customer_product_product_id.

unique keeps every pair different, which the two-column primary key requires. 100 customers and 40 products allow 4,000 pairs, so 600 rows are well within the limit.

The same request with each rule

Changing rule in the bridge field changes how often each customer and each product appears. Every result below has unique set, so every pair is different. The average is 6 rows for each customer and 15 for each product.

`rule`	`stdDev`	Rows	Rows per customer	Customers not used	Rows per product
`Random`		600	2 to 13	0	10 to 22
`Distributed`		600	6	0	15
`Unique`		40	0 or 1	60	1
`Clustered`	2	600	2 to 12	0	10 to 20
`Clustered`	5	600	0 to 19	7	2 to 22
`ClusteredDistributed`	5	600	1 to 19	0	4 to 27

Random uses customers and products by chance, so the counts vary.
Distributed uses every customer exactly 6 times and every product exactly 15 times.
Unique uses each customer and each product in one row at most. The product table has 40 values, so rowCount must be 40 or fewer. With 600 rows, the request is rejected.
Clustered spreads the counts by stdDev. At stdDev 5, some customers are not used at all.
ClusteredDistributed spreads the counts in the same way, and uses every customer and every product at least once.

To use one of the clustered rules, set stdDev with it:

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "rightTargetTable": "product",
  "rightTargetColumn": "product_id",
  "rule": "ClusteredDistributed",
  "stdDev": 5,
  "unique": true,
  "map": {
    "left": { "column": "customer_id", "dataType": "Integer" },
    "right": { "column": "product_id", "dataType": "Integer" }
  }
}

The same request with each side weighted

With the Random rule, each side can be weighted by a column of its own table. This bridge field favors Business customers and Blue products, and leaves out Green products:

{
  "type": "Reference",
  "targetTable": "customer",
  "targetColumn": "customer_id",
  "weightColumn": "segment",
  "weights": [
    { "value": "Business", "weight": 3 },
    { "value": "Retail", "weight": 1 }
  ],
  "rightTargetTable": "product",
  "rightTargetColumn": "product_id",
  "rightWeightColumn": "color",
  "rightWeights": [
    { "value": "Blue", "weight": 2 },
    { "value": "Red", "weight": 1 }
  ],
  "rule": "Random",
  "unique": true,
  "map": {
    "left": { "column": "customer_id", "dataType": "Integer" },
    "right": { "column": "product_id", "dataType": "Integer" }
  }
}

The 100 customers are 49 Business and 51 Retail. The 40 products are 14 Blue, 11 Red and 15 Green. The rows of customer_product, by the customer's segment and the product's color:

Side	Value	Rows
Customer `segment`	`Business`	436
Customer `segment`	`Retail`	164
Product `color`	`Blue`	418
Product `color`	`Red`	182
Product `color`	`Green`	0

Each Business customer is three times as likely to appear as each Retail customer, and each Blue product twice as likely as each Red product. Green is not in rightWeights, so no green product appears. Every pair is still different, and both foreign keys are declared in the same way.

Table of Contents