Determines the kind of child table model the provider exposes.

Data Type

bool

Default Value

false

Remarks

By default the component exposes a backwards-compatible data model that is not fully relational. In this mode non-child tables have a primary key called Document.Id, but child tables do not have a primary key. Instead they have a column called Document.Id which has the same value as the Document.Id of the parent row that contains the child row.

For example, a parent table invoices containing invoice records may look like this:

And its child invoices_lineitems containing line items may look like this:

This model has several limitations:

• Complex JOIN results may be incorrect. In most cases the component can translate a JOIN like SELECT * FROM invoices INNERT JOIN invoices_lineitems ON invoices.[Document.Id] = invoices_lineitems.[Document.Id] into an UNNEST. But if the JOIN is too complex then both sides are executed separately which can produce incorrect results.
• DML operations on nested child tables are impossible because there is no way to specify what row of the middle child to use. For example, you cannot change rows in a table like invoices_lineitems_discounts because there is no way to specify the lineitem that contains the discount you are updating.
• Some environments like SSIS may not be able to operate on child tables at all because they do not have primary keys.

The NewChildJoins data model is fully relational. In this mode non-child tables have the same Document.Id as before, but child tables are extended to have both a foreign key and a primary key. The foreign key is called Document.Parent and it refers to the Document.Id of the row in the parent table that contains the child row. The primary key is called Document.Id and it contains a path which uniquely refers to that child row.

For example, the same tables as above would look like this in the NewChildJoins model. invoices would be the same:

However, invoices_lineitems would have both a primary and foreign key. The primary key contains the ID of the parent row as well as the child row's position in the parent.

This fixes the limitations of the old data model:

• Complex JOIN results are always consistent because they link foreign keys to primary keys. SELECT * FROM invoices INNERT JOIN invoices_lineitems ON invoices.[Document.Id] = invoices_lineitems.[Document.Parent]
• DML operations on nested child tables are allowed because the Document.Id contains all the required information to pick out specific rows, regardless of the table's depth.
• Environments which depend on primary keys can use these tables and generate JOIN queries since the relationships between Document.Id and Document.Parent columns are included in the component metadata.